Use dax variable inside filter

[kgrafton86]

I am trying to filter a table based on how another table is already filtered by building a variable and using it in the filter, but I seem to be doing something wrong. Here is a sample of the data I am working with:

I have built a filtered version of the table using the following code:

Filtered_ALL_METRICS_patient_pivot = 
    VAR _ConditionsList = 
        IF ( CONCATENATEX(DISTINCT(VALUES(AllPatients[CAD])), [CAD], " ") = "1", ", 'CAD'", "" )
        & IF ( CONCATENATEX(DISTINCT(VALUES(AllPatients[CHF])), [CHF], " ") = "1", ", 'CHF'", "" )
        & IF ( CONCATENATEX(DISTINCT(VALUES(AllPatients[COPDAsthma])), [COPDAsthma], " ") = "1", ", 'COPDAsthma'", "" )
        & IF ( CONCATENATEX(DISTINCT(VALUES(AllPatients[Diabete])), [Diabete], " ") = "1", ", 'Diabete'", "" )
        & IF ( CONCATENATEX(DISTINCT(VALUES(AllPatients[HLD])), [HLD], " ") = "1", ", 'HLD'", "" )
        & IF ( CONCATENATEX(DISTINCT(VALUES(AllPatients[HTN])), [HTN], " ") = "1", ", 'HTN'", "" )
    VAR _FilterList =  IF ( LEN ( _ConditionsList ) = 0, "'CAD', 'CHF', 'COPDAsthma', 'Diabete', 'HLD', 'HTN'", RIGHT ( _ConditionsList, LEN ( _ConditionsList ) - 2 ) )
    RETURN 
    FILTER(ALL_METRICS_patient_pivot, ALL_METRICS_patient_pivot[metric] IN {_FilterList})

But no matter what I have selected, the filtered table is empty. What am I doing wrong?

I currently do not have relationships set up between the filtered table and any other table. I have tried connecting it to several other tables that are being filtered correctly, but this did not give me any rows in the filtered table.

I have confirmed that _FilterList is outputting what I intend (e.g., 'CAD', 'CHF', 'COPDAsthma', 'Diabete', 'HLD', 'HTN' if everything or nothing is selected, and 'CHF', 'Diabete' if just those two conditions are selected) by creating a metric that is the same but returns _FilterList instead. I included an x in front of the variable names in the metric to avoid any potential scoping issues.

Any help is appreciated, as dax is not my strong point!

[animixplay]

I'm trying to optimize my DAX formula by using a variable inside a filter context. Has anyone successfully implemented this? I’m curious about the best practices and any tips you might have to share

[lbendlin]

Remember that a variable is materializing.  Use it mainly if you want to re-use the result without impact from a filter context.

[tharunkumarRTK]

@LFM 

I think you created two posts for the same question, I have answered this question here: https://community.fabric.microsoft.com/t5/Desktop/Issues-with-getting-data-from-PBI-to-excel/m-p/412...

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[lbendlin]

You need to let the data model do that filtering work for you, or worst case use TREATAS to project the filter in one table onto another table.