Forum Discussion

ndo03001's avatar
ndo03001
Frequent Visitor
6 years ago
Solved

Combined columns with unique values into single filter

I have 6 columns each with unique values. However, the values are related so I want a single filter to impact the visual instead of six separate filters. In the image below: Col1. unique identifier. ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi ndo03001 ,

     

    Please add a condition to the original logic and the overall count will be correct.

    Here's the modified formula.

    GM1 = 
IF (
    ISFILTERED ( GoalTable[t] ),
    IF (
        MAX ( 'Appended_CCSSE_2015_2019'[GOALAA] ) = SELECTEDVALUE ( GoalTable[t] )
            || MAX ( 'Appended_CCSSE_2015_2019'[GOALCERT] ) = SELECTEDVALUE ( GoalTable[t] )
            || MAX ( 'Appended_CCSSE_2015_2019'[GOALCHGCAR] ) = SELECTEDVALUE ( GoalTable[t] )
            || MAX ( 'Appended_CCSSE_2015_2019'[GOALJOBSKILL] )
                = SELECTEDVALUE ( GoalTable[t] )
            || MAX ( 'Appended_CCSSE_2015_2019'[GOALSELFIMP] ) = SELECTEDVALUE ( GoalTable[t] )
            || MAX ( 'Appended_CCSSE_2015_2019'[GOALTR4YR] ) = SELECTEDVALUE ( GoalTable[t] ),
        1,
        0
    ),
    1
)

    The result would be shown as below.

    PBIX as attached.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi ndo03001 ,

 

If i understand you correctly, please check the following steps as below.

1# Create a calculated table.

Table 2 = DISTINCT(UNION(SELECTCOLUMNS('Table',"t",'Table'[C1]),SELECTCOLUMNS('Table',"t",'Table'[C2]),SELECTCOLUMNS('Table',"t",'Table'[C3])))

2# Create measures.

Measure = IF(MAX('Table'[C1])=SELECTEDVALUE('Table 2'[t])||MAX('Table'[C2])=SELECTEDVALUE('Table 2'[t])||MAX('Table'[C3])=SELECTEDVALUE('Table 2'[t]),1,0)
Measure 2 = SUMX('Table',[Measure])

 3# Add 'table 2'[t] as slicer.

Result would be shown as below.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ndo03001's avatar
    ndo03001
    Frequent Visitor
    6 years ago

    I followed the steps but it isn't impacting the visual. Do I need to use the Measure as a visual level filter?  I want to display the data in Col 2 titled OOCIDEAS in the visual and use the filter to adjust the returned results for that visual.  

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi ndo03001 ,

       

      You don't need to use the measure as a visual level filter.

      Here's my pbix, hopefully works for you.

       

      Best Regards,

      Jay

      Community Support Team _ Jay Wang

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • ndo03001's avatar
        ndo03001
        Frequent Visitor
        6 years ago

        I have hit a wall trying to figure out where I went wrong.  

        The goal of the report is to show how many survey respondents selected a particular response to a question.  Pretty standard stuff. What I want to be able to do is then slice that visual by a number of variables.  The problematic variable is the one where the data was recorded across six fields. So I want to be able to select one of the fields without a forced hierarchy.

        Using your method the only way to make the filter work is to change both the value in the slicer (to the calculated table field 't') and the value of the visual from a count of Survey IDs to Measure2. When I use that method the counts change and don't represent accurate data (page 1 of the attached .pbix) The overall count drops dramatically so I must have misstepped along the way. When I leave the viz using the count of Survey IDs the slicer doesn't work (page 2 attached .pbix).

        I tried testing it out in a fresh viz with a limited dataset of just the fields I'm interested in to clear the slate and I am getting the same result. 

        Viz