CODEWITHSUNDEEP
sql-serverpowerbidaxbusiness-intelligencedaxstudio
Arpit Sharma
Arpit Sharma

Reputation: 138

Power BI DAX scenario for a complex logic with multiple sub category

I am stuck in one of the scenario for powerbi.

Below is the dummy data, I want to consider product with latest state in the count against respective subcategory.

E.g. product PRD1 has latest state followup i.e. 5 against sub category xyz so that should be consider under Subcategory xyx only.

Also product PRD5 has latest state ongoing i.e. 3 against sub category "abc" and "xyz" so it should be consider in count against both the sub categories.

Can anyone please suggest any idea how can I handle this situation. Since I tried numerous approach to handle logic but nothing is working as I expected

enter image description here

Category SubCategory Product State   CalcColumn
CAT         abc       PRD1   ongoing     3
CAT         xyz       PRD1   started     2
CAT         xyz       PRD1   ongoing     3
CAT         xyz       PRD1   finished    4
CAT         xyz       PRD1   followup    5
CAT         abc       PRD2   ongoing     3
CAT         xyz       PRD2   started     2
CAT         xyz       PRD2   ongoing     3
CAT         xyz       PRD2   finished    4
CAT         xyz       PRD2   followup    5
CAT         xyz       PRD3   started     2
CAT         xyz       PRD3   ongoing     3
CAT         xyz       PRD3   finished    4
CAT         xyz       PRD3   followup    5
CAT         pqr       PRD4   finished    4
CAT         abc       PRD5   ongoing     3
CAT         xyz       PRD5   ongoing     3
CAT         xyz       PRD6   waiting     1
CAT         xyz       PRD6   started     2

Expected output

Category Subcategory ProductCount
CAT         abc           5
CAT         xyz           1
CAT         pqr           1

Please note that we have slicers for category and subcategory. If we select any particular value under sub category the count will be change as per filter selection

Thanks in advance!

Upvotes: 0

Views: 782

Answers (1)

Mik
Mik

Reputation: 2103

How about this?

-- get the maximum CalcColumn value for each product
VAR t =
    CALCULATETABLE(
        ADDCOLUMNS(
            VALUES('MyTable'[Product])
            ,"CalcColumn",CALCULATE(MAX('MyTable'[CalcColumn]))
        )
        -- you can play with keepfilters, or All(), or remove Calculatetable() to adjust the result you want
       ,ALL('MyTable')
   )
RETURN 
    CALCULATE(
        COUNTROWS('MyTable')
        ,TreatAS(t,'MyTable'[Product],'MyTable'[CalcColumn])
    )

Upvotes: 1

Related Questions