Forum Discussion
Creating Logic to find whether columns in one table matches other columns in other table
Excellove15 , the problem is we have no relationship between Door and Store. Calculating StatusDoor and StatusStore and combing it with IF we have some kind of CrossJoin. IF() returns 0 for each store not in filtered door. We need to return BLANK() instead, measure will be
_Breach = VAR Statuses = MAX ( Door[Status] ) & [_Status] VAR Breach = IF ( ISBLANK ( [_Status] ), BLANK (), IF ( Statuses = "OpenC", 1, 0 ) ) RETURN IF ( HASONEVALUE ( Door[SiteId] ), Breach, BLANK () )To calculate total just iterate it on all doors
Breach Total = SUMX ( Door, [_Breach] )Finally
File is B&M (4).pbix
Hi Sir,
Just to confirm, have you had a chance to look into the above query?
I would really appreciate any guidance or suggestions.
I mentioned above that 'The DateTime column in Store Status Table matches the CreatedOn column in Door Table'. But door table createdon date column is not in half hour format.
we can change this column to round off to near by half hour(in power query) to match the Store status Datetime column.
Thanks in advance!
Excellove15 , sorry, I'm quite busy on this week. Perhaps later.