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,...
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!
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!
B&M-Copy is not available, I took previous available version.
Relationships. In Calendar you have Date type (time is always zero), in other tables DateTime. Thus no related records. I added to Store Status table column
Date = INT( 'Store Status'[DateTime] )in Door table
Date = INT( Door[DateTimeRounded] )and adjust relationships with Calendar accordingly.
Also added inactive one-to-many relationship between Site and Door
And make inactive between Site and Device
Measures adjusted as
Doors Status Total-01 = VAR _count = MAX( Door[CreatedOn] ) RETURN CALCULATE( COUNTROWS(Door), Door[CreatedOn] = _count, USERELATIONSHIP( Door[SiteId], Site[SiteCode] ) ) ////////// Doors Open-01 = VAR _count = CALCULATE( MAX( Door[CreatedOn] ), Door[Status] = "Open" ) RETURN CALCULATE( COUNT( Door[CreatedOn] ), Door[CreatedOn] = _count, Door[Status] = "Open", USERELATIONSHIP( Door[SiteId], Site[SiteCode] ) ) ////////// Doors Closed-01 = VAR _count = CALCULATE( MAX( Door[CreatedOn] ), Door[Status] = "Closed" ) RETURN CALCULATE( COUNT( Door[CreatedOn] ), Door[CreatedOn] = _count, Door[Status] = "Closed", USERELATIONSHIP( Door[SiteId], Site[SiteCode] ) )With that it looks like
but left bottom table is not filtered properly, we have another Store id except 169. Will try to check what's wrong some later.
Hi SergeiBaklan Sir,
Many thanks for your quick response and your solution🙂
It means a lot to me! yes this solution works great! can i get this file?
Many thanks Sir!
