cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Need help to write with DAX Formula

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

1 ACCEPTED SOLUTION
Super User

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors