Hey @Anonymous ,
that's a good question.
I personally would work with internal tables. I would summarize YEAR, ID and Product to get rid of duplicate rows and then see if the internal table has 2 rows (A&B) or only 1 row (either A or B).
So my approach would be the following:
TEST COL NEW =
-- save the Year and ID of the current row
VAR vRowYear = 'Test Table'[Year]
VAR vRowID = 'Test Table'[ID]
-- Summarize table by Year, ID and Product and filter for values in current row
-- This in case you will have more columns later to get rid of duplicates
VAR vFilteredTable =
FILTER(
SUMMARIZE(
'Test Table',
'Test Table'[Year],
'Test Table'[ID],
'Test Table'[Product]
),
'Test Table'[Year] = vRowYear && 'Test Table'[ID] = vRowID
)
-- If the table filtered for A and B contains 2 rows it's A and B
VAR vProdAnB = COUNTX(vFilteredTable, IF( 'Test Table'[Product] = "A" || 'Test Table'[Product] = "B", 1)) = 2
-- If the table filtered for A contains 1 row it's only A
VAR vProdA = COUNTX(vFilteredTable, IF('Test Table'[Product] = "A", 1)) = 1
-- If the table filtered for B contains 1 row it's only B
VAR vProdB = COUNTX(vFilteredTable, IF('Test Table'[Product] = "B", 1)) = 1
RETURN
SWITCH(
TRUE(),
vProdAnB, "A & B",
vProdA, "A Only",
vProdB, "B Only"
)
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
