topic Re: Group each person only to one group in DAX Commands and Tips

Group each person only to one group

hlynurgudna — Tue, 08 Mar 2022 13:46:30 GMT

Hi all

I have this problem where I am trying to group together all the names which have the "I" marker and put them only in group X, and the other names that dont have the market "I" go in group Y.

I have been trying out different version of IF statements, Textcontains etc. but what happens then is that I always group for example name A in both group X and Y, when I only want to have it in group X

This is an example of my data, each person always has more than one marker per day so if I try something like if(name="I";"X";"Y")

Person A goes into both group X and Y

Name	Marker	Date
A	I	8.3.2022
A	IN	8.3.2022
A	8	8.3.2022
B	IN	8.3.2022
B	8	8.3.2022
C	I	9.3.2022
C	9	9.3.2022
C	F	9.3.2022
D	I	9.3.2022
D	9	9.3.2022
D	F	9.3.2022

Here is an example of my desired output

Name	Date	Group
A	8.3.2022	X
B	8.3.2022	X
C	9.3.2022	Y
D	9.3.2022	Y

Best regards

Re: Group each person only to one group

johnt75 — Tue, 08 Mar 2022 15:41:08 GMT

If the summary table you want to create does not need to be affected by any filters or slicers then I would create a calculated table as

Summary Table = DISTINCT( 'Table'[Name] )

and then create a 1-to-many relationship from that to your data table.

You could then add a calculated column as

Group = IF ( "I" IN SELECTCOLUMNS( RELATEDTABLE( 'Table' ), "@marker", 'Table'[Marker]), "X", "Y" )

Its not clear which date field is required. If its the minimum date then you could add that as MINX( RELATEDTABLE( 'Table'), 'Table'[Date]).

Re: Group each person only to one group

hlynurgudna — Tue, 08 Mar 2022 16:27:24 GMT

Hi John

Thanks for the answer. I forgot to mention that person A could be in group X on 8.3.2022 but be in group Y the day after, I´m sorry that I didnt include it in my data

So we could se example where this could happen

Name	Marker	Date
A	I	8.3.2022
A	IN	8.3.2022
A	8	8.3.2022
A	IN	9.3.2022
A	8	9.3.2022

In this case person A should be in group X 8.3.2022 but in group Y 9.3.2022

Best regards

Re: Group each person only to one group

johnt75 — Tue, 08 Mar 2022 16:39:24 GMT

OK, I think a summary table is probably still the way to go, it just needs a few tweaks.

Summary Table = SUMMARIZE( 'Table', 'Table'[Name], 'Table'[Date])

You won't need to create a relationship from the summary table to the base table, you should be able to add the Group column as something like

Group = 
var currentName = 'Table'[Name]
var currentDate = 'Table'[Date]
var filterTable = TREATAS( { ( currentName, currentDate) }, 'Table'[Name], 'Table'[Date])
var valuesTable = CALCULATETABLE( SELECTCOLUMNS( 'Table', "@marker", 'Table'[Marker]), filterTable )
var result = IF ( "I" IN valuesTable, "X", "Y" )
return result

Re: Group each person only to one group

tamerj1 — Tue, 08 Mar 2022 18:18:51 GMT

Hi hlynurgudna
One way to that

Group = IF ( { "I" } IN SELECTCOLUMNS ( 'Table', "Marker", 'Table'[Marker] ), "X", "Y" )

Re: Group each person only to one group

hlynurgudna — Wed, 09 Mar 2022 09:18:08 GMT

Great, this works! Many thanks for the help