Setting a column's value according to a filter selection

[Marra13]

I have a data table as follows:

Vertical  Metric        Value
Europe   Revenue       100
Europe   Hours             50
US          Revenue       200
US          Hours             60
Asia        Revenue      300
Asia        Hours            70

and two slicers, the first allows the user to select Metric or Vertical, if the user selects Metric the second slicer displays Europe, US, Asia, the user selects one, say Europe and the report table displays:

Revenue 100
Hours      50

Likewise, if the user selects Vertical, the second slicer displays Revenue, Hours, the user selects Revenue and the report table displays:

Europe 100
US        200
Asia      300

How do I do this? I've tried creating a new column in the data table called RowLabel and using the following DAX to set it but it doesn't work: RowLabel = SWITCH([SelectedFilter1], "Metrics", Table[Vertical], "Verticals", Table[Metric])
where [SelectedFilter1] = SELECTEDVALUE(FiltersTable[Filter1]). There is no relationship between the data and filter tables.

[I've got it working by repeating the data in the data table with the Vertical and Metric column values swapped in the second half, but surely a neat solution is available through DAX]

[Jihwan_Kim]

Hi, @Marra13 

I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below.

Value Measure =

SWITCH (

SELECTEDVALUE ( SlicerTable[Slicer] ),

"Metric",

CALCULATE (

SUM ( Data[Value] ),

TREATAS ( VALUES ( FilterTable[Content] ), Data[Metric] )

),

"Vertical",

CALCULATE (

SUM ( Data[Value] ),

TREATAS ( VALUES ( FilterTable[Content] ), Data[Vertical] )

),

BLANK ()

)

Value Measure Fix =

VAR slicertwoselect =

SELECTEDVALUE ( SlicerTable[SlicerTwo] )

RETURN

CALCULATE (

[Value Measure],

FILTER (

Data,

Data[Vertical] = slicertwoselect

|| Data[Metric] = slicertwoselect

)

)

https://www.dropbox.com/s/u6dw267iz0s7amv/marra.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Marra13]

To clarify (I'm very sorry but I got the slicer combinations mixed up in my first post).

Slicer 1 has two options: Vertical and Metric

Slicer 2's options depend on the selection of slicer 1; if Vertical is chosen the options displayed in slicer 2 are: Europe, US and Asia, if Metric is chosen in slicer 1 then slicer 2 displays Revenue and Hours. So if the user selects Vertical and Europe then the report table looks like this:

Metric      Value

Revenue      100

Hours            50

however if the user selects Metric and Revenue then the report table looks like this:

Vertical      Value

Europe         100
US                200
Asia              300

The value column is not the problem, it's the first column, the row label. In the first output table the label comes from the column Metric whilst in the second the label comes from the column Vertical. I created another column in the data table called RowLabel and tried to assign the values from column Metric or Vertical to it depending on what the user selects in slicer 1.

Hope this helps.

[Jihwan_Kim]

Hi, @Marra13 

Thank you for your feedback.

Please check the below.

https://www.dropbox.com/s/u6dw267iz0s7amv/marra.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM