topic Re: first apply slicer value, then group by and then concatenate/roll-up cells in DAX Commands and Tips

first apply slicer value, then group by and then concatenate/roll-up cells

dkeerthi — Fri, 18 Mar 2022 21:27:45 GMT

I have the below table as raw data:

How do i generate the below output table for slicer value - 2021 and 2022. I want to first apply the slicer value on year column, then group the procedures by person, then get a count. For ex: john has 2 procedures A & B - so want to concantenate A+B and count is as 1.

Re: first apply slicer value, then group by and then concatenate/roll-up cells

m3tr01d — Sat, 19 Mar 2022 00:30:56 GMT

hi dkeerthi
I don't think there's an easy way to do this.
I got something but it's not really flexible.

You can make a Calculated Table with this code

Procedures = CALCULATETABLE( SUMMARIZECOLUMNS( Data[Person], "Procedure", CONCATENATEX( VALUES( Data[Procedure] ), Data[Procedure], "+", Data[Procedure], ASC ) ), Data[Year] IN {2021,2022} )

Then, you can make a measure to count the number of rows of this table :

Person_Count = COUNTROWS( Procedures )

Then, you should be able to drag the field Data[Procedure] on the table with [Person_Count] on values.

Maybe, someone has a better solution 🙂

Re: first apply slicer value, then group by and then concatenate/roll-up cells

dkeerthi — Mon, 21 Mar 2022 18:23:04 GMT

just wondering on how do i pass slicer values for years?

Re: first apply slicer value, then group by and then concatenate/roll-up cells

v-yalanwu-msft — Tue, 22 Mar 2022 02:03:46 GMT

Hi, dkeerthi ;

You could create a measure as follows:

Procedure2 = CONCATENATEX('Data',[Procedure],"+")

as count : Through your description, I don't quite understand what your counting logic is, please correct me if I understand wrong.

count = var _count=CALCULATE(COUNT(Data[Procedure]),FILTER(ALLSELECTED(Data),[Person]=MAX([Person]))) return IF(_count>1,1,2)

The final output is shown below:

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

Re: first apply slicer value, then group by and then concatenate/roll-up cells

v-yalanwu-msft — Wed, 30 Mar 2022 06:19:24 GMT

Hi， dkeerthi ；

Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

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