Forum Discussion

Radz2707's avatar
Radz2707
Regular Visitor
1 year ago
Solved

Hey team ! Need help creating a filter based on columns .

I have survey data , having customer info, it has 50 flag columns having yes/ no values . I have to create a slicer having all these flag column names , such that if I click on a flag letters say Has...
Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi Radz2707 ,

    As you mentioned that, you data is confidential. you can't able to share sample data. Based on your inputs , i have created sample data.

     

    Please follow below steps.

     

    1. Created sample data based on your column headers. please refer snap.

     

    2.  Created disconnected table, to reprsent the flag names in slicer based on below DAX code.

    FlagSelector = DATATABLE(
        "FlagName", STRING,
        {
            {"IsCitizen"},
            {"HasPolicy"},
            {"HasPro"},
            {"HasPremium"},
            {"HasPPU"}
        }
    )
     
    3.  Created measure with below DAX code.
     
    FlagFilterMeasure =
    VAR SelectedFlag = SELECTEDVALUE(FlagSelector[FlagName])
    RETURN
        SWITCH(
            TRUE(),
            SelectedFlag = "IsCitizen", IF(MAX(SurveyData[IsCitizen]) = "Yes", 1, 0),
            SelectedFlag = "HasPolicy", IF(MAX(SurveyData[HasPolicy]) = "Yes", 1, 0),
            SelectedFlag = "HasPro", IF(MAX(SurveyData[HasPro]) = "Yes", 1, 0),
            SelectedFlag = "HasPremium", IF(MAX(SurveyData[HasPremium]) = "Yes", 1, 0),
            SelectedFlag = "HasPPU", IF(MAX(SurveyData[HasPPU]) = "Yes", 1, 0),
            1
        )
     
    4. In  table visual, add Custono, SurveyWave columns. Add FlagSelector[FlagName] in slicer. and drag 'FlagFilterMeasure' measure in visual level filter and set the value ='1'.
     
    5. Please refer the output snap and attched PBIX file.

     

    If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
    Thank you.

Shravan133's avatar
Shravan133
Icon for Super User rankSuper User
1 year ago

Generated by AI

 

🛠️ Solution Outline (Without Unpivoting)

  1. Create a Disconnected Table for Flags

Manually define a table listing all your flag column names.

Option 1: Use DAX to create it

FlagSelector =

DATATABLE(

    "FlagName", STRING,

    {

        {"IsCitizen"},

        {"HasPolicy"},

        {"HasPro"},

        {"HasPremium"},

        {"HasPPU"}

    }

)

Or create it in Power Query if you prefer.

  1. Create a Measure to Filter Based on Selected Flag

Here’s the trick: write a measure that checks the selected flag and filters rows accordingly.

SelectedFlagFilter =

VAR SelectedFlag = SELECTEDVALUE(FlagSelector[FlagName])

RETURN

    SWITCH(

        TRUE(),

        SelectedFlag = "IsCitizen", CALCULATE(COUNTROWS(SurveyData), SurveyData[IsCitizen] = "Yes"),

        SelectedFlag = "HasPolicy", CALCULATE(COUNTROWS(SurveyData), SurveyData[HasPolicy] = "Yes"),

        SelectedFlag = "HasPro", CALCULATE(COUNTROWS(SurveyData), SurveyData[HasPro] = "Yes"),

        SelectedFlag = "HasPremium", CALCULATE(COUNTROWS(SurveyData), SurveyData[HasPremium] = "Yes"),

        SelectedFlag = "HasPPU", CALCULATE(COUNTROWS(SurveyData), SurveyData[HasPPU] = "Yes"),

        BLANK()

    )

That measure gives you dynamic results — you can also use a version returning 1/0 (TRUE/FALSE) to use as a visual filter.

  1. Use it as a Visual-Level Filter

To filter visuals based on the selected flag:

  • Create a Boolean measure:

ShowRow =

VAR SelectedFlag = SELECTEDVALUE(FlagSelector[FlagName])

RETURN

    SWITCH(

        TRUE(),

        SelectedFlag = "IsCitizen", SurveyData[IsCitizen] = "Yes",

        SelectedFlag = "HasPolicy", SurveyData[HasPolicy] = "Yes",

        SelectedFlag = "HasPro", SurveyData[HasPro] = "Yes",

        SelectedFlag = "HasPremium", SurveyData[HasPremium] = "Yes",

        SelectedFlag = "HasPPU", SurveyData[HasPPU] = "Yes",

        TRUE()

    )

Then, drag this measure to the filter pane of your visuals, and set it to TRUE.

🔄 Result

Now, your slicer shows:

IsCitizen | HasPolicy | HasPro | HasPremium | HasPPU

And selecting, say, HasPro dynamically filters your visuals to only show customers where HasPro = "Yes" — without unpivoting or restructuring your data model.

⚠️ Notes

  • This method supports only 1 selection at a time. For multi-select, more complex logic (e.g. using IN) is needed.
  • The more flags you have, the longer the SWITCH gets — you might consider managing it via a helper table and relationship in more advanced models.