Forum Discussion

Excellove15's avatar
Excellove15
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's avatar
    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's avatar
SergeiBaklan
Diamond Contributor
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's avatar
    Excellove15
    Iron Contributor
    Feb 25, 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 https://1drv.ms/u/c/cfada767f73d87ed/EbAQnstjs5tBthCCQRJu3_wB5OehUGX65CjhOaeolzMjbQ?e=iC0foO

     

    Many Thanks in advance!

    • sivarajan's avatar
      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