cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
DLcito
New Member

Get unique combinations of several columns

I have a table Date and I want the visible unique combinations of 2 columns: IsHoliday and Calendar Year

 

 ALL ( 'Date'[Is Holiday], 'Date'[Calendar Year] ) gives me all the existent combinations of both columns but it's immune to filter context. You can see that in the following image:

 

DLcito_0-1685543436244.png

 

I need it to be susceptible to filters 

 

What DAX function could I use? 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You can't create an actual table, as that will never take into account the filter context. What you can do is create the table as a variable in your measures, e.g.

VAR FilteredHolidays = CALCULATETABLE(
SUMMARIZE( 'Date', 'Date'[Is Holiday], 'Date'[Calendar year])
)

Once you have the variable you can use it as a filter or to iterate over it

View solution in original post

1 REPLY 1
johnt75
Super User
Super User

You can't create an actual table, as that will never take into account the filter context. What you can do is create the table as a variable in your measures, e.g.

VAR FilteredHolidays = CALCULATETABLE(
SUMMARIZE( 'Date', 'Date'[Is Holiday], 'Date'[Calendar year])
)

Once you have the variable you can use it as a filter or to iterate over it

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors