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.
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