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
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 https://1drv.ms/u/c/cfada767f73d87ed/EWoyW8uWGadGokrYOgPFq2sBqH1a63Se582ubBbGLH3uaw?e=eVuGaM
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 https://1drv.ms/u/c/cfada767f73d87ed/EfFsvl-viaNLtF1RPxuVkDkByszr7aMoT6aQF8M-rqCbvw?e=QTsX1l
Please let me know if you still can't download it.
Many thanks Sir!