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

[dkeerthi]

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. 

[v-yalanwu-msft]

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.

[v-yalanwu-msft]

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.

[dkeerthi]

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

[m3tr01d]

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 🙂