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

[Radz2707]

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 HasPro , it should filter all visuals by HasPro=yes. 
P.S. I can't really unpivot the data as there are 15columns . 
a sample could be 

cust no . | survey wave| response date| iscitiezen | has policy | has pro | has premium | has PPU . 
All the columns after response date are having yes/ no values.Please note : these columns are not mutually exclusive so I can't create a single calculated column , that can be further used in a slicer.

[Radz2707]

Thanks , for the help team , however , my use case is a bit different and I had confiedential data in my data set do I was unable to Provide the sample . I tried all the above solutions , all did part ofthe job , but my requirement is a bit diffferent .

[v-dineshya]

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.

[v-dineshya]

Hi @Radz2707 ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Thank you

[v-dineshya]

Hi @Radz2707 ,

We haven’t heard from you on the last response and was just checking back to see , please provide sample data And, if you have any further query do let us know.

Thank you.

[danextian]

Hi @Radz2707 

What does this mean?

P.S. I can't really unpivot the data as there are 15columns . 

 It would be easier for anyone to provide a more feasible solution  had you provided a sample data which doesn't have to be confidential but actually represents the actual data, and your expected result from that. Ensure that we can easily copy-paste it.

[maruthisp]

Hi Radz2707,

I tried to implement a solution based on your original post. Please check the below pbix file.
creating a filter based on columns.pbix

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[ryan_mayu]

could you pls provide some sample data (not only the headers) and the expected output?

[Ashish_Excel]

Hi,

I do not understand what you mean by "I can't really unpivot the data as there are 15columns"?  Why can you not unpivot?

[Shravan133]

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.

2. 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.

3. 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.