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.

Radz2707's avatar
Radz2707
Regular Visitor
1 year ago

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's avatar
v-dineshya
Icon for Community Support rankCommunity Support
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.

  • v-dineshya's avatar
    v-dineshya
    Icon for Community Support rankCommunity Support
    1 year ago

    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.

    • v-dineshya's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      1 year ago

      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.

    • Radz2707's avatar
      Radz2707
      Regular Visitor
      1 year ago

      Thank you so much v-dineshya . This did help with half of the job , I did some modelling to the data and source level . and together with this approach , i was able to acieve the desired output .