Measure to include/exclude values in slicer

[heidibb]

I have a simple measure of sum(success)/sum(studentcount).

I also have a slicer for the student's academic program.

I want to create one measure for the students in the programs selected on my slicer.... and then another measure for everyone excluding those in the programs selected in my slicer. 

Thoughts?

[v-juanli-msft]

Hi

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @heidibb 

I make a test as below:

1. create a table, have no relatiponship with other table

program slicer = VALUES('Table'[program])

2. create measures

Assume sum(success)/sum(studentcount)

sum(success)->count of stutents who's scores larger or equal to 3

sum(studentcount)->all count of students

selected pro = IF(SELECTEDVALUE('program slicer'[program])=MAX('Table'[program]),1,0)

countall of stutents = CALCULATE(DISTINCTCOUNT('Table'[stu.no]),ALL('Table'))

success count = CALCULATE(DISTINCTCOUNT('Table'[stu.no]),FILTER('Table','Table'[score]>=3))

selected = CALCULATE([success count],FILTER(ALL('Table'),[selected pro]=1))/[countall of stutents]

NO selected = CALCULATE([success count],FILTER(ALL('Table'),[selected pro]=0))/[countall of stutents]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @heidibb 

Could you give more details how to caluclate sum(success)/sum(studentcount)?

There are some useful acrticles you could refer to for the scenario below

https://exceleratorbi.com.au/items-not-selected-slicer/

https://www.daxpatterns.com/basket-analysis/#downloads

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB]

Hi @heidibb 

It would help if you provide some sample data (in tabular format).

You could create a dynamic table within your measure along the lines of:

VAR AuxTable_ = EXCEPT(ALL(Table1); Table1)

where Table1 is your table. AuxTable_ will have all the rows that are not included in the slicer. You can then operate with AuxTable_