Logic to find whether columns in one table matches columns in another column not directly related

[sivarajan21]

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, I need to create a logic(Breach) to find when Door Table - Status column being Open when Store Status table - Status column being Closed (indicated by - 'C').

During this logic, we have to make sure it satisfies below conditions:

• DateTime column of Store Status table should match the createdon column of 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.
• Store id column of Store Status table should match the siteid of Door table

Output should return 1 if the conditions meets else 0.

Could you please help me create a logic for this?

PFA file here B&M.pbix

Thanks in advance!

@marcorusso @tharunkumarRTK @Ahmedx @Greg_Deckler 

[Anonymous · Accepted solution]

Hi @sivarajan21 

The best workaround is to merge the 2 tables based on the rounded createdon column (from the Door table) and the DateTime column (from the Store Status table), and make sure that the siteid column from the Door table matches the Store_id column from the Store Status table.
You can create a new calcualted table:

ADDCOLUMNS (
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS (
                'Door',
                'Door'[SiteId],
                'Door'[Status],
                'Door'[CreatedOn],
                "_CreatedOnTime",
                    IF (
                        MINUTE ( 'Door'[CreatedOn] ) > 15
                            && MINUTE ( 'Door'[CreatedOn] ) < 45,
                        DATE ( YEAR ( 'Door'[CreatedOn] ), MONTH ( 'Door'[CreatedOn] ), DAY ( 'Door'[CreatedOn] ) )
                            + TIME ( HOUR ( 'Door'[CreatedOn] ), 30, 00 ),
                        IF (
                            MINUTE ( 'Door'[CreatedOn] ) <= 15,
                            DATE ( YEAR ( 'Door'[CreatedOn] ), MONTH ( 'Door'[CreatedOn] ), DAY ( 'Door'[CreatedOn] ) )
                                + TIME ( HOUR ( 'Door'[CreatedOn] ), 0, 00 ),
                            IF (
                                MINUTE ( 'Door'[CreatedOn] ) >= 45,
                                DATE ( YEAR ( 'Door'[CreatedOn] ), MONTH ( 'Door'[CreatedOn] ), DAY ( 'Door'[CreatedOn] ) )
                                    + TIME ( HOUR ( 'Door'[CreatedOn] ), 60, 00 )
                            )
                        )
                    )
            ),
            'Store Status'
        ),
        [_CreatedOnTime] = 'Store Status'[DateTime]
            && 'Door'[SiteId] = 'Store Status'[StoreID]
    ),
    "Breach",
        IF ( 'Door'[Status] = "Open" && 'Store Status'[Status] = "C", 1, 0 )
)

But given the amount of data is too large, the workaround might be running slowly.

After my examination of the data, you can try the following:

First of all, create a calculated column in the Door table:

_CreatedOnTime =
IF (
    MINUTE ( 'Door'[CreatedOn] ) > 15
        && MINUTE ( 'Door'[CreatedOn] ) < 45,
    DATE ( YEAR ( 'Door'[CreatedOn] ), MONTH ( 'Door'[CreatedOn] ), DAY ( 'Door'[CreatedOn] ) )
        + TIME ( HOUR ( 'Door'[CreatedOn] ), 30, 00 ),
    IF (
        MINUTE ( 'Door'[CreatedOn] ) <= 15,
        DATE ( YEAR ( 'Door'[CreatedOn] ), MONTH ( 'Door'[CreatedOn] ), DAY ( 'Door'[CreatedOn] ) )
            + TIME ( HOUR ( 'Door'[CreatedOn] ), 0, 00 ),
        IF (
            MINUTE ( 'Door'[CreatedOn] ) >= 45,
            DATE ( YEAR ( 'Door'[CreatedOn] ), MONTH ( 'Door'[CreatedOn] ), DAY ( 'Door'[CreatedOn] ) )
                + TIME ( HOUR ( 'Door'[CreatedOn] ), 60, 00 )
        )
    )
)

Then add the 3 measures:

SiteName =
VAR _currentDT =
    MAX ( 'Door'[_CreatedOnTime] )
VAR _currentSiteID =
    MAX ( 'Door'[SiteId] )
RETURN
    CALCULATE (
        CONCATENATEX ( 'Store Status', 'Store Status'[Site Name] ),
        FILTER (
            'Store Status',
            'Store Status'[DateTime] = _currentDT
                && 'Store Status'[StoreID] = _currentSiteID
        )
    )

_Status =
VAR _currentDT =
    MAX ( 'Door'[_CreatedOnTime] )
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 )

Finally add a table visual, the result is as follow:

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sivarajan21]

Hi @Anonymous  sir,

This is an amazing solution and wonderfully crafted!🤩

You are truly a masterpiece and i still can't believe that this issue got resolved.

You came like a saviour for this community! If you are in linkedin please provide me your id  so that i can write a short endorsement for your "Community Building" or "Power BI" skills.
Your guidance saved me hours of frustration—I really appreciate your expertise!

I will skip the calculated table that was created. Because i have billions of rows so that will cause performance issues. I will go with dax measures. Just to confirm, do we need the inactive relationship that was created in your file if we use your dax measures?

Many thanks sir!

@Anonymous 

[Anonymous]

Hi  @sivarajan21 

No, the inactive relationship in the file is one of my attempts, you can remove it, it won't have an impact on the result.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.