Filtering Matrix values

[M-Sayeed]

Hi

My matrix is showing a country's exports and it has calendar years in the columns and export products in the rows. I can use export dollar values and volumes (in tonnes) as the Matrix Values but want to use slicer which will allow me show either the dollar values or the tonnes but not the both. Is it possible to create such a slicer/filter?

[rajendraongole1 · Accepted solution]

Hi @M-Sayeed -Yes, it is possible to create a slicer in Power BI that allows you to toggle between two measures, such as export dollar values and volumes in tonnes, and display only one in your matrix.

You can do this by creating a "Measure Selector" with a disconnected table and some DAX logic.

Create a new table with the names of the two options you'd like to toggle between (e.g., "Dollar Values" and "Tonnes").
You could name this table something like Measure Selector.

Go to Modeling > New Measure and create a measure that uses SWITCH and SELECTEDVALUE to toggle between the dollar values and tonnes

Selected Measure =
SWITCH(
SELECTEDVALUE('Measure Selector'[Measure]),
"Dollar Values", SUM('ExportData'[Dollar Values]),
"Tonnes", SUM('ExportData'[Tonnes])
)

Drag the Measure field from the Measure Selector table to the slicer.This will allow users to toggle between "Dollar Values" and "Tonnes".

hope this helps

[Anonymous · Accepted solution]

Hi, @M-Sayeed 

Based on your information, I create a sample table:

Then create a new table:

SlicerTable = DATATABLE(
    "Option", STRING,
    {
        {"Dollar Values"},
        {"Volumes"}
    }
)

Create a new measure, and put the Option column in slicer view. Put measure in matrix visual:

SelectedValue = 
SWITCH(
    SELECTEDVALUE('SlicerTable'[Option]),
    "Dollar Values", SUM('Table'[DollarValue]),
    "Volumes", SUM('Table'[Volume (Tonnes)])
)

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[M-Sayeed]

Hi v-yohua-msft

Preview

Thanks a lot for your spot-on solution. It solved my problem very perfectly in the Power BI Desktop RS version. But the probem is when I upload it on Power BI server it is not accepting the report anymore. From discussions in this forum, posted by others ,I understood that Power BI server cannot handle DAX query view. In that case I cannot use New Table and New Measure functions. Now I can create a Table with an Option column where I have "Dollar Values" and "Volumes" rows from Excel to avoid using New Table function. Is there any way I can create your SelectedValue measure in excel which I then can be imported into PowerBI report? Thanks.

[M-Sayeed]

It worked for me perfectly. Thanks a lot, mate. 

[rajendraongole1 · Accepted solution]

Hi @M-Sayeed -Yes, it is possible to create a slicer in Power BI that allows you to toggle between two measures, such as export dollar values and volumes in tonnes, and display only one in your matrix.

You can do this by creating a "Measure Selector" with a disconnected table and some DAX logic.

Create a new table with the names of the two options you'd like to toggle between (e.g., "Dollar Values" and "Tonnes").
You could name this table something like Measure Selector.

Go to Modeling > New Measure and create a measure that uses SWITCH and SELECTEDVALUE to toggle between the dollar values and tonnes

Selected Measure =
SWITCH(
SELECTEDVALUE('Measure Selector'[Measure]),
"Dollar Values", SUM('ExportData'[Dollar Values]),
"Tonnes", SUM('ExportData'[Tonnes])
)

Drag the Measure field from the Measure Selector table to the slicer.This will allow users to toggle between "Dollar Values" and "Tonnes".

hope this helps

[M-Sayeed]

Very helpful. Thanks a lot, mate.