Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Martin-Prague
Helper II
Helper II

Create dynamic DAX table filtered by slicer from another table

Hello Comunity,

 

I would like to know, If I can create DYNAMIC DAX TABLE which is taking filter from selection by slicer in the report, and use this selection to dynamically filter this table. The slicer is from another table in optimal scenario.

 

Simple example in the same table with static filter - works:

 

 

Marketing Objective dynamic = FILTER(ALL('KPI List'[Marketing Objective]); 'KPI List'[Marketing Objective] = "Build Awareness")

 

I would like to make this last part dynamic and this value should be comming from diffrenet table  and this value should be selected in slicer:

 

 

 

= "Build Awareness")

 

 

At the first time I tried to use the same table with SELECTED VALUE but the result is NO VALUES retrieved into this table:

 

Marketing Objective dynamic = FILTER(ALL('KPI List'[Marketing Objective]); 'KPI List'[Marketing Objective] = SELECTEDVALUE('KPI List'[Marketing Objective]))

 

 

My idea is maily to cover the need:

 

To combine these tables KPI list and Campaing Master Data controlled by slicers from KPI List where are all of my combinatios of Marketing Objetive and Media Type located in order to deliver expected behavior of slices to end users.

Idea.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

As we have limitation for M:N relationships I need to use intersections tables, but the problem is when I use these intersections tables, I do not have the expected behaviour of my Slices - these filters should have interactions with each other and should show only the combinations of data that are located in KPI list.

 

 

Expected behaviour:

In slicer when I select MO1 for Marketing Objective then MT1, MT2, MT3 should be only shown in second slicer. But with these intersections all MTs are shown in the Slicer.

 

 

Marketing ObjectiveMedia Type
MO1MT1
MO1MT2
MO1MT3
MO2MT4
MO2MT5

 

 

 

I was thinking that I can solve this with my example that I have at the begening described. But it does not work either.

 

Maybe there is someone who can help me on this, maybe with OUT-OF-THE-BOX thinking.

 

 

Thanks,

 

Martin

1 ACCEPTED SOLUTION
Brian_M
Responsive Resident
Responsive Resident

Hi Martin, welcome to the community!

 

A couple of key principles:

 

1. Calculated Tables ('DAX Tables') that you can see in the Relationships screen of Power BI Desktop are calculated whenever the model is first processed (e.g. if you apply model changes from Power Query, or when you first open the model), so they can never be affected by the selection of a slicer by a user. 

 

2.  In the model you show in your screenshot, any filter from the slicer on the Marketing Type applies from the one side (Marketing Type) down to the many side (both Campaing Master Data and KPI List), but the filter cannot flow upstream from either the KPI List or Campaing Master Data (many side) back up to the Media Type table (one the one side). 

 

I would:

  a) create a compound key in Power Query for both Campaing Master Data & KPI List ( e.g. CompoundKey = [MarketObjective]&"-"&[MediaType] ),

  b) then I would create a DAX table Bridge = SUMMARIZE ( 'KPI List', 'KPI List'[CompoundKey] ) (you could also do this in Power Query)

  c) I would create relationships as shown below, note in particular the bi-directional relationship between Bridge and KPI List

 

The tables and relationships are shown here: 

 

Relationships.JPGReport.JPG

 

 

Now when you place a slicer on Market Objective in KPI List, you will see it filters the other column Media Type in the same table giving you the filtering behaviour you desire. The relationship can flow from the many side (KPI List) to the one side (Bridge) due to the bi-directional relationship, and from the Bridge down to the Campaing Master Data table via the normal one-to-many relationship.

 

This model may not achieve exactly what you need but hopefully it gives you some ideas.

 

 pbix file

View solution in original post

2 REPLIES 2
Brian_M
Responsive Resident
Responsive Resident

Hi Martin, welcome to the community!

 

A couple of key principles:

 

1. Calculated Tables ('DAX Tables') that you can see in the Relationships screen of Power BI Desktop are calculated whenever the model is first processed (e.g. if you apply model changes from Power Query, or when you first open the model), so they can never be affected by the selection of a slicer by a user. 

 

2.  In the model you show in your screenshot, any filter from the slicer on the Marketing Type applies from the one side (Marketing Type) down to the many side (both Campaing Master Data and KPI List), but the filter cannot flow upstream from either the KPI List or Campaing Master Data (many side) back up to the Media Type table (one the one side). 

 

I would:

  a) create a compound key in Power Query for both Campaing Master Data & KPI List ( e.g. CompoundKey = [MarketObjective]&"-"&[MediaType] ),

  b) then I would create a DAX table Bridge = SUMMARIZE ( 'KPI List', 'KPI List'[CompoundKey] ) (you could also do this in Power Query)

  c) I would create relationships as shown below, note in particular the bi-directional relationship between Bridge and KPI List

 

The tables and relationships are shown here: 

 

Relationships.JPGReport.JPG

 

 

Now when you place a slicer on Market Objective in KPI List, you will see it filters the other column Media Type in the same table giving you the filtering behaviour you desire. The relationship can flow from the many side (KPI List) to the one side (Bridge) due to the bi-directional relationship, and from the Bridge down to the Campaing Master Data table via the normal one-to-many relationship.

 

This model may not achieve exactly what you need but hopefully it gives you some ideas.

 

 pbix file

Hello Brian_M,

 

thank you so much, Thanks so much for explanation on the fist one.

I am now investigating this behaviour in PBI, currently it seems to be what I want!

 

Big thanks,

 

Martin

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.