Forum Discussion

kgrafton86's avatar
kgrafton86
Frequent Visitor
2 years ago

Use dax variable inside filter

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!

Questions

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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.

  • animixplay's avatar
    animixplay
    New Member
    1 year ago

    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's avatar
      lbendlin
      Super User
      1 year ago

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