Measure as visual level filter in matrix not working as expected

[sietseasml]

Hi, 

I have a report with multiple cascading KPIs (see sample below). The goal is to compare these KPIs across a selection of machines.

The reason for choosing a marix, is that the number of KPIs is quite large and they are grouped under a parent and do not neccessarily share the same calculation method.   

The problem I have is that I want to make the selection of columns in the matrix dynamic, based on a measure. 
Something like below, where based on a certain measure, it should show the column yes or no. 

MeasureFilter = IF(MAX(EQUIPMENT[EQUIPMENT_NR]) = [DynamicallySelectedEquipment], 1, 0)
-- DynamicallySelectedEquipment hardcoded to "BBBB" here 

When I add this measure as a filter to the visual. It becomes completely blank. 

 

However, when I add this measure as a value it returns the expected results for each row. How do I filter a matrix on a measure like this? Where it would only show column BBBB in this case. I checked online and read that it should evaluate the measure expression for each row in the matrix and filter rows accordingly. But it doesn't seem to do that here.

Possibly because of the hierarchical measure I have to display the cascading KPIs?

Below the measure used to get the different KPI values:

HierarchicalMeasure = 

SWITCH(
    TRUE,
    ISINSCOPE(MeasureHierarchy[Level2]), 
        SWITCH(
            TRUE,
            SELECTEDVALUE(MeasureHierarchy[Level2]) = "KPI1.1", "Value KPI1.1",
            SELECTEDVALUE(MeasureHierarchy[Level2]) = "KPI1.2", "Value KPI1.2",

            SELECTEDVALUE(MeasureHierarchy[Level2]) = "KPI2.1", "Value KPI2.1",
            SELECTEDVALUE(MeasureHierarchy[Level2]) = "KPI2.2", "Value KPI2.2",
            BLANK()
        ),
    ISINSCOPE(MeasureHierarchy[Level1]), 
        SWITCH(
            TRUE, 
            SELECTEDVALUE(MeasureHierarchy[Level1]) = "KPI1", "Value KPI1",
            SELECTEDVALUE(MeasureHierarchy[Level1]) = "KPI2", "Value KPI2",
            BLANK()
        )
)

Where MeasureHierarchy is a static table defining the KPI names and hierarchy structure. 

Level1	Level2
KPI1	KPI1.1
KPI1	KPI1.2
KPI2	KPI2.1
KPI2	KPI2.2

Would appreciate if someone could help or suggest an alternative way to approach this problem.

[v-pagayam-msft]

Hi @sietseasml ,
I wanted to check and see if you had a chance to review our previous response. Please let me know if everything is sorted or if you need any further assistance.

Thank you.

[v-pagayam-msft]

Hi @sietseasml ,
Could you please confirm if the issue has been resolved on your end?  If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

[v-pagayam-msft]

Hi @sietseasml ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.

[v-pagayam-msft · Accepted solution]

Hi @sietseasml ,
Thank you for reaching out to us on Microsoft Fabric Community Forum!

1.Try using below measure and add it in Values field. I tried to recreate it locally.Please find the attached file.

HierarchicalMeasure =

SWITCH(

    TRUE,

    ISINSCOPE(MeasureHierarchy[Level2]),

        SWITCH(

            TRUE,

            SELECTEDVALUE(MeasureHierarchy[Level2]) = "KPI1.1", SUM(KPIValues[Value]),

            SELECTEDVALUE(MeasureHierarchy[Level2]) = "KPI1.2", SUM(KPIValues[Value]),

            SELECTEDVALUE(MeasureHierarchy[Level2]) = "KPI2.1", SUM(KPIValues[Value]),

            SELECTEDVALUE(MeasureHierarchy[Level2]) = "KPI2.2", SUM(KPIValues[Value]),

            BLANK()

        ),

    ISINSCOPE(MeasureHierarchy[Level1]),

        SWITCH(

            TRUE,

            SELECTEDVALUE(MeasureHierarchy[Level1]) = "KPI1", SUM(KPIValues[Value]),

            SELECTEDVALUE(MeasureHierarchy[Level1]) = "KPI2", SUM(KPIValues[Value]),

            BLANK()

        )

)

If this solution meets your requirement,consider accepting it as solution.

Regards,
Pallavi.

[sietseasml]

Read through the article, but couldn't make it work 100% yet. 

I looked through the DAX query with performance analyzer, I can get the expected results when I add one single line in the query. I'm not sure why this is not there when the DAX query is generated for the visual. Is there a way to adjust the dax code generated?

In __ValueFilterDM3, without my line, it summarizes on 'MeasureHierarchy' columns and then filters based on MeasureFilter. But I still need the EQUIPMENT_NR context here. Any clue on how to add this? 

[johnt75]

SQLBI have an excellent article at https://www.sqlbi.com/articles/deep-dive-into-measure-filters-in-power-bi-desktop/ detailing the subtle differences when a measure is used as a filter when it is and is not shown in the values as well.

I think the simplest solution in your case would be to add the measure as a value as well as a filter, and set the width of the column in the matrix to 0.

[sietseasml]

Thanks for your reply. Article seems exactly what I am looking for, I will read through it. 

Adding the measure as both a value and filter unfortunately does not work immediately. It is at least populating values for each column now, but the hierichal measure is still blank.