Slicing Using Multiple Columns Using AND Logic

[rachaellearns]

Hello,

I have a dataset that looks something like this:

student_id	status	gender	race_ethnicity	special_ed	ell	gifted
1	on_track	F	Black or African American	0	0	1
2	off_track	M	White	1	0	0
3	on_track	M	White	0	0	1
4	on_track	F	Hispanic/Latino	0	1	1
5	off_track	F	Hispanic/Latino	0	0	0
6	on_track	M	Black or African American	0	0	1
7	on_track	F	White	0	0	1
8	off_track	M	Asian	0	0	1
9	off_track	F	White	1	0	0

I want to show the percentage of students in each demographic group who are "on track" and "off track" in one visualization, with a slicer that enables users to select a demographic attribute to view. I want users to be able to select multiple attributes that combine using 'and' logic. For example, if users simply want to see how Asian students are performing, they can select 'Asian' from the slicer, and the % of off track and on track Asian students will appear. If users would like to see how Asian males are performing specifically, users can select 'Male' and 'Asian', and the graph will adjust.

  (Right now, I have a separate visualization (Clustered column chart) for each demographic column (one visualization for race, one for gender, one for english language learners, ect.) The axis is the demographic column, the legend is the status column, and the value is the following measure:

Measure = DISTINCTCOUNT('table'[student_id])/CALCULATE(DISTINCTCOUNT('table'[student_id]), ALL('table'[status]))

So, for example, in the gender table, Male and Female appear on the axis. In the male section, there are two columns, one representing the percentage of on track male students (50%), and one representing the percentage of off track male students(50%). The same is setup appears for female students. )

I have tried unpivoting the gender - gifted columns and using the resulting single column to slice the data, but this does not give me the desired result.  I have also tried creating a slicer table, but that did not work. 

Please help... any guidance on this would b greatly appreciated. 

[parry2k]

@rachaellearns try this measure

Measure = DISTINCTCOUNT('table'[student_id])/
CALCULATE(DISTINCTCOUNT('table'[student_id]), ALLSELECTED('table'))

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[rachaellearns]

Hi @parry2k, thank you for your response. This works in a way, but it does not work exactly how I would like it to work.  

I would like a slicer that uses and logic - so if Asian & Male is selected in the slicer, student number 8, who is Asian and male, populates (along with any other Asian males in the dataset). Right now, if I select Asian and male, all students who are either Asian or male are included. 

The slicer is set up as follows:

1. I combined the gender, race, special ed, ell, and gifted columns into one column and called that column "alldemo". 

2. I created a slicer table like this:

SlicerTable = DISTINCT(
    UNION(
        SELECTCOLUMNS(
            'table', 
            "Selection", 'table'[gender], 
            "All Demographics", 'table'[alldemo]),
        SELECTCOLUMNS(
            'table',
            "Selection", 'table'[race_ethnicity],
            "All Demographics", 'table'[alldemo]),
        SELECTCOLUMNS(
            'table',
            "Selection", 'table'[gifted],
            "All Demographics", 'table'[alldemo]),
        SELECTCOLUMNS(
            'table',
            "Selection", 'table'[ell],
            "All Demographics", 'table'[alldemo]),
        SELECTCOLUMNS(
            'table',
            "Selection", 'table'[special_ed],
            "All Demographics", 'table'[alldemo]
)))

 I then linked the "All Demographics" column in 'SlicerTable' to the alldemo column in 'table'

Next, I create the slicer using the "Selection" column in 'SlicerTable'. The "Selection" column is also the axis on the viz.