Search code examples
powerbivisualizationfield

power bi dynamically change measure values and names with filters

I have a slicer with two values A and B(single select).

I also have two measures that countrows based on some condition, [M1] and [M2]. I need to use these measures in some visuals( a matrix/ stacked column chart etc). When filter A is applied, I need to show [M1] values only and when filter B is applied i need [M2] values only,

I was able to do this using a dax switch measure or field parameter But the field names are not as expected in the visuals

Using switch: M1/M2= SWITCH(SELECTEDVALUE( Table[Filtercolumn]),"A", [M1],"B", [M2], BLANK())

In matrix visual I get the following result:

Id M1/M2
1 12
2 20

Even though the values(count) is populating correctly when filters are applied the heading remains as M1/M2, but I need either M1 or M2 based on filter selected

when using field parameters:

when filter A applied

Id M1 M2
1 12
2 20

when filter B applied

Id M1 M2
1 34
2 26

Here the heading and values are proper but blank columns are not required.

Sample screenshot:

Matrix visual with blank M2 values

Stacked column visual with Y axis values having both M1 and M2

Is there any work around to get below result when filter A applied:

Id M1
1 12
2 20

when filter B is applied:

Id M2
1 34
2 26
Solution

  • Update your Table to have two columns like:

    FilterColumn LabelColumn
    A M1
    A G1
    A S1
    A B1
    B M2
    B G2
    B S2
    B B2

    Now use the LabelColumn in the Matrix visual in Columns, and use your M1/M2 measure in Values of the Matrix. For other visuals, use the LabelColumn for Legends. That should be it.

    Note: Not possible to do this with the plain Table visual. Columns will always be visible and you cannot dynamically name the columns, in the Table visual.


    Update

    If you aleady have four measure for Mx, Gx, Sx, Bx then add one more column like so:

    FilterColumn LabelColumn Category
    A M1 M
    A G1 G
    A S1 S
    A B1 B
    B M2 M
    B G2 G
    B S2 S
    B B2 B

    Then create a new measure that looks like this:

    Meaningful Name = SWITCH( SELECTEDVALUE(Table[Category]),
  "M", [M1/M2],
  "G", [G1/G2],
  "S", [S1/S2],
  "B", [B2/B2]
)