Group each person only to one group

[hlynurgudna]

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

[johnt75 · Accepted solution]

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

[tamerj1 · Accepted solution]

Hi @hlynurgudna 
One way to that 

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

[hlynurgudna]

Great, this works! Many thanks for the help

[johnt75 · Accepted solution]

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

[johnt75]

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]).

[hlynurgudna]

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