Need help to write with DAX Formula

[Anonymous]

Hello community, 

I want to create a column for the customer who has just product A, Product B and both products A and B. I have written the DAX formula, but can you please advise me on writing in a more intuitive way?

Thankyou in anticipation,

Chanas

[selimovd · Accepted solution]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic