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

SergeiBaklan

MVP

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!

sivarajan
Copper Contributor
Feb 27, 2025
Hi SergeiBaklan Sir,

I will close this query as of now and open a new thread if i have any queries!🙂
It means a lot to me you helped me save lot of frustration hours.

Many thanks

Forum Discussion

Creating Logic to find whether columns in one table matches other columns in other table

Share

Resources