CODEWITHSUNDEEP
powerbidax
Aibloy
Aibloy

Reputation: 157

Selected filters by default - Power BI

Imagine that I have a column with the following values: [A1,A2,A3,A4,T1,T2,T3]

Now, I have a slicer with the mentioned column, but I need that the the "Ts" (T1,T2 and T3) are always selected / filtered and when I choose a specific "A" (A1) for example, dosn't filter the "T".

For example:

If I select with the slicer "A1" the filter in the page should include: [A1,T1,T2,T3]

If I select "A2": [A2,T1,T2,T3]

So If I select an "A" it have to select the specific "A" and all the "Ts".

How can I do that? With filters? relationships?

Thanks

Upvotes: 1

Views: 549

Answers (1)

sergiom
sergiom

Reputation: 4887

Short answer: we need a parameter table.

This because we might be able to write a measure that internally sets the filter according to our specification, but the Power BI visual would intercept the slicer setting first and would show only the selected rows or columns (for a matrix visual).

Assuming we have the table

T table

this measure ignores any selection from the slicer, computing the total of the whole column, but the matrix visual will only show the row selected with the slicer

TotalV = SUMX(ALL(T), T[V] )

matrix with only filtered rows

To solve this we can build a parameter table with the combination we want when selecting the F parameter, for instance like the following Parameter table

Parameter table

The column P is to be used in the slicer, while the column F is used to set up a bi-directional relationship with the original T table

Now we create the relationship

relationship

We create the measure

SumV = SUM(T[V])

setting the slicer over 'P' and a matrix with 'T[F]' on the rows and the measure '[SumV]' as value we obtain the desired behavior

final matrix

Additional considerations:

  • the Parameter table can be generated manually or using a DAX calculated table
  • to build a better model we might create a dimension for F
  • the bidirectional relationship in this configuration doesn't make the model ambiguous, but we must pay attention when adding tables and relationships to the model

Upvotes: 1

Related Questions