Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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

 

Channa_0-1629743010201.png

 

1 ACCEPTED SOLUTION
selimovd
Super User
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
 

View solution in original post

1 REPLY 1
selimovd
Super User
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
 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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