Create dynamic DAX table filtered by slicer from another table

[Martin-Prague]

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.

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 Objective	Media Type
MO1	MT1
MO1	MT2
MO1	MT3
MO2	MT4
MO2	MT5

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

[Brian_M · Accepted solution]

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: 

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

[Martin-Prague]

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