Measure with Sum and group by, and use this measure in slicer

[Anonymous]

Hi Experts,

I have a requirement where I need to find the patients who have thier total dispense days greater than certain days (adjustable using slicer) for a particular drug.

I have attached sample pbix file to help. 

https://drive.google.com/file/d/1AC_mi1FB_fc0-L24G9-zoTU0kzGj0loK/view?usp=sharing

I would have two slicers in the report, one for the dispense date and the other on the total number of dispense days.

I am looking at having a measure that sums the dispense days grouped by the patient and the drug and then use this as the one of the slicer above.

Appreciate your help here.

[Anonymous · Accepted solution]

The below code met my requirement.

Measure 5 = CALCULATE(SUM(DispenseTable[Drug Dispensed Days]), FILTER(ALLSELECTED(DispenseTable),DispenseTable[PatientId] = Max(DispenseTable[PatientId])),FILTER(ALLSELECTED(DrugTable),DrugTable[MP] = Max(DrugTable[MP])))

https://drive.google.com/file/d/1AC_mi1FB_fc0-L24G9-zoTU0kzGj0loK/view?usp=sharing

The solution in the below post helped me:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Group-by-in-the-measure/m-p/2160496

[Anonymous]

Thanks @amitchandak .

Would something similar to the solution in the below would work?

https://community.powerbi.com/t5/Desktop/DAX-SUM-with-GROUP-BY/m-p/703429

Please find updated pbix file in the link that I am trying to work but I don't get the same results as in the above solution.

My pbix: https://drive.google.com/file/d/1AC_mi1FB_fc0-L24G9-zoTU0kzGj0loK/view?usp=sharing

[amitchandak]

@Anonymous , Seem like you are looking for Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ