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. Col2. Survey question that I want the visual to be based on. Col 3-8 are qualities that I want to combine into a single filter.

Snapshot of data

Using an IF statement didn't work because it forced a hierarchy on the values when I want the value to show up each time. For example SurveyNo 07065074 should display in the visual whether I select Earn Assoc, Transfer, OR Career Change since those were all selected characteristics by the survey respondent.
I also tried the following:

New table 

GoalCollege2 = DISTINCT (
UNION (
SELECTCOLUMNS ( Appended_CCSSE_2015_2019, "T", Appended_CCSSE_2015_2019[GOALAA] ),
SELECTCOLUMNS ( Appended_CCSSE_2015_2019, "T", Appended_CCSSE_2015_2019[GOALCHGCAR] ),
SELECTCOLUMNS ( Appended_CCSSE_2015_2019, "T", Appended_CCSSE_2015_2019[GOALCERT]),
SELECTCOLUMNS ( Appended_CCSSE_2015_2019, "T", Appended_CCSSE_2015_2019[GOALJOBSKILL]),
SELECTCOLUMNS ( Appended_CCSSE_2015_2019, "T", Appended_CCSSE_2015_2019[GOALSELFIMP]),
SELECTCOLUMNS ( Appended_CCSSE_2015_2019, "T", Appended_CCSSE_2015_2019[GOALTR4YR])
) )
I made NO connection between the tables
Then created six measures
GoalAAFinal = CALCULATE(VALUES(Appended_CCSSE_2015_2019[GOALAA]), FILTER(Appended_CCSSE_2015_2019,Appended_CCSSE_2015_2019[GOALAA]=SELECTEDVALUE('GoalCollege2'[T])))
GoalCarFinal = CALCULATE(VALUES(Appended_CCSSE_2015_2019[GOALCHGCAR]), FILTER(Appended_CCSSE_2015_2019,Appended_CCSSE_2015_2019[GOALCHGCAR]=SELECTEDVALUE('GoalCollege2'[T])))
GoalCertFinal = CALCULATE(VALUES(Appended_CCSSE_2015_2019[GOALCERT]), FILTER(Appended_CCSSE_2015_2019,Appended_CCSSE_2015_2019[GOALCERT]=SELECTEDVALUE('GoalCollege2'[T])))
GoalJobFinal = CALCULATE(VALUES(Appended_CCSSE_2015_2019[GOALJOBSKILL]), FILTER(Appended_CCSSE_2015_2019,Appended_CCSSE_2015_2019[GOALJOBSKILL]=SELECTEDVALUE('GoalCollege2'[T])))
GoalJoyFinal = CALCULATE(VALUES(Appended_CCSSE_2015_2019[GOALSELFIMP]), FILTER(Appended_CCSSE_2015_2019,Appended_CCSSE_2015_2019[GOALSELFIMP]=SELECTEDVALUE('GoalCollege2'[T])))
GoalTRFinal = CALCULATE(VALUES(Appended_CCSSE_2015_2019[GOALTR4YR]), FILTER(Appended_CCSSE_2015_2019,Appended_CCSSE_2015_2019[GOALTR4YR]=SELECTEDVALUE('GoalCollege2'[T])))
 
I then created a filter 
FilterCollegeGoal = IF(Appended_CCSSE_2015_2019[GoalAAFinal]||Appended_CCSSE_2015_2019[GoalCarFinal]||Appended_CCSSE_2015_2019[GoalCertFinal]||Appended_CCSSE_2015_2019[GoalJobFinal]||Appended_CCSSE_2015_2019[GoalJoyFinal]||Appended_CCSSE_2015_2019[GoalTRFinal],1,0)
 
I created a slicer based on GoalCollege2[T]
 
For the visual, the Axis is Year, Legend OOCIDEAS, and Value is Count of SURVEYNO.  I added a visual level filter FilterCollegeGoal Is 0
 
Any suggestions on where I went wrong?

 

 

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.

6 Replies

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