Forum Discussion

Iron Contributor

Feb 17, 2025

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

admin

BI & Data Analysis

Excel on mobile

Formulas and Functions

Macros and VBA

power bi

training

SergeiBaklan
Feb 25, 2025
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

Iron Contributor

Feb 26, 2025

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!