Dynamic Set operations - Intersection, Union in a visual based on user date selections

[ashishd]

Hello friends,

I have been struggling to do a very simple operation in PowerBI and seek your help for the same.

I have simplified the use case here with the below data.

Date1: 5/1/2024 
Date2: 5/2/2024
Intersection list: Empty

Date1: 5/1/2024 
Date2: 5/3/2024
Intersection list: {A, D}

I have to provide the user the ability to select any 2 specific dates and then based on the 2 selected dates, show the common items on those 2 dates (intersection) or show items present only on the first date but missing on the second date, etc. kind of set operation results.

The challenge for a new comer to Power BI from Tableau is that in Tableau there are Date Parameters which can be embedded in the formulas and this kind of report can easily be created which is dynamic and user can select different dates and instantly see the result. But I am unable to do the same in PowerBI.

I tried creating 2 separate date tables from which I created 2 date slicers for user selection. But I am stuck in writing the formula as the Calculated column by definition cannot update at run time by changing the date slicers and I don't know how to make this dynamic date driven formula otherwise in PowerBI.

Please help.

Thank you!

Ashish

[ashishd]

Thanks a lot @TomMartens - This solves my problem.

Thanks @ThxAlot - This approach has also taught me.

[ThxAlot]

One disconnected date table is enough,

[TomMartens]

Hey @ashishd ,

consider creating a pbix file that contains sample data, but still reflects your semantic model (tables, relationships, calculated clumns, and measures). Upload the pbix to OneDrive, Google Drive, or dropbox and share the link. If you are using a spreadsheet to create the sample data, instead of manual input method, share the spreadsheet as well.

Regards,

Tom

[ashishd]

Hey @TomMartens ,

Please check this shared folder with required files: 
https://drive.google.com/drive/folders/1KQW-EHwtnl74htwmaiuV1NWrL6GeIKZx?usp=sharing

Thank you!

Ashish

[TomMartens · Accepted solution]

Hey @ashishd ,

here, you will find your pbix that now contains two measures and a new table: https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EQd2IMfzWjRAqvPx1pUeNGYBEjo-U...

Please be aware that my approach is using only the date table Date1 that is NOT connected to the "fact" table Sheet1.

The additional table is simple: it provides a "simple" llist of joinjypes, that i use as a slicer. This allows a user to select a join type, currently I have the join tpyes implemented:

• intersect
• leftanti (items only available in the first date)
• rightanti (items only available in the second date)

There is a simple measure "vizAid DateSelection" that shows a text depending on the the number of selected dates.

The not so simple measure "Measure" basically creates two tables containing different sets of items.

Depending of the selected join type one condition of the FILTER function will be activated. This approach is necessary because DAX does not allow that a table or column object can be created in the TRUE or FALSE branch of a conditional statement like IF or SWITCH.

This is how my adaption looks like:

Hopefully this provides what you are looking for.

Regards,

Tom