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
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!
Hi Sir,
I have resolved this by creating the below measures🙂
_Status = VAR _currentDT = MAX('Door'[DateTimeRounded]) VAR _currentSiteID = MAX('Door'[SiteId]) RETURN CALCULATE(CONCATENATEX('Store Status','Store Status'[Status]),FILTER('Store Status','Store Status'[DateTime] = _currentDT&&'Store Status'[StoreID] = _currentSiteID))_Breach = IF([_Status] = "C"&&SELECTEDVALUE(Door[Status]) = "Open",1,0)I have used both these dax measures in table visual and they are working great!
But the only problem i have is, i need to count the Breach(where it says 1) using a dax measure to be used in KPI card.
Please help me with the logic whenever you find time.PFA file here B&M.pbix
Thanks in advance!
Great to know you solved an issue. As for the question new file is not downloadable
Hi Sir,
Apologise for the inconvenience caused!
PFA file here B&M.pbix
Please let me know if you still can't download it.
Many thanks Sir!
