Forum Discussion
Solved
Creating Logic to find whether columns in one table matches other columns in other table
Hi Team,
I have 2 tables:
Door table as below:
Store Status table as below:
They are modelled as below (let me know if we can better model it-suggestions are welcomed):
Now, I need to create a logic(Breach) to find when Door Table - Status column being Open when Store Status table - Status column being Closed (indicated by - 'C').
During this logic, we have to make sure it satisfies below conditions:
- DateTime column of Store Status table should match the createdon column of Door table
- Store id column of Store Status table should match the siteid of Door table
Output should return 1 if the conditions meets else 0.
Could you please help me create a logic for this?
PFA file here B&M.pbix
Thanks in advance!
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
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 SergeiBaklan Sir,
Thanks for your quick response!🙂
Thats really amazing and am overwhelmed to see this😍 today morning!
Especially the way in which you made the left table to work was amazing! hats off!
I will have a look into the Breach total and get back sir.
I was calculating Alert on our OpenstateDuration table and the logic(Not sure whether it is efficient) for calculated column :
Alert = VAR CurrentDevice = 'OpenstateDuration'[DeviceId] VAR CurrentCon = 'OpenstateDuration'[CreatedOn] VAR MaxOpenMinutesForDevice = CALCULATE( MAX('OpenstateDuration'[OpenMinutes]), FILTER( ALL('OpenstateDuration'), 'OpenstateDuration'[DeviceId] = CurrentDevice && 'OpenstateDuration'[Status] = "Open" ) ) RETURN IF( 'OpenstateDuration'[Status] = "Closed" && 'OpenstateDuration'[OpenMinutes] >= 60, "Yes", IF( 'OpenstateDuration'[Status] = "Open" && 'OpenstateDuration'[OpenMinutes] = MaxOpenMinutesForDevice, "Yes", "No" ) )We have billions of data so thought to push this logic in power query.
Can you please help me Sir?
PFA updated file B&M (4).pbix
Many Thanks in advance!
Hi SergeiBaklan Sir,
I will close this query as of now and open a new thread if i have any queries!🙂
It means a lot to me you helped me save lot of frustration hours.
Many thanks
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!Not sure I understood how you measures work. For example, let take North Shields, it has Doors Open
But if we apply any filter, dashboard shows nothing
What it shall be here?
Hi SergeiBaklan Sir,
I am shocked to see the results of my dax logic why its not showing any output🙄when it should give a count:
The dax logic that i used for kpi cards such as Doors Open is below:
Doors Open = VAR _count = CALCULATE(SELECTEDVALUE(Door[CreatedOn]),Door[Status] = "Open") RETURN CALCULATE(COUNT(Door[CreatedOn]),Door[CreatedOn] = _count,Door[Status] = "Open")This is worrying me and am struggling to understand.
Could you please help me resolve this?
Are you able to download the file?
if not PFA B&M - Copy.pbix
Thanks in advance!
Excellove15 , sorry, I'm quite busy on this week. Perhaps later.
Hi Sir,
Nop! I completely understand🙂
I am trying this by myself and will keep you updated
Many thanks sir!
