Forum Discussion

VamshiKumar's avatar
VamshiKumar
Frequent Visitor
4 years ago
Solved

DAX Measure for skipping rows based on stages

Hi Team,   I have been stuck at a Dax formula and couldn't solve it after spending a considerable amount of time. I request you to please help with this.   My Table would look like below.   ...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    VamshiKumar  you can use a measure like this

    Measure2 =
CALCULATE (
    SUM ( 'Table 1'[Revenue] ),
    EXCEPT (
        CALCULATETABLE (
            SUMMARIZE ( 'Table 1', 'Table 1'[Candidate ID] ),
            'Table 1'[Status] IN { "Joined", "Abscond" }
        ),
        SUMMARIZE (
            EXCEPT (
                CALCULATETABLE (
                    SUMMARIZE ( 'Table 1', 'Table 1'[Candidate ID], 'Table 1'[Status] ),
                    'Table 1'[Status] IN { "Joined", "Abscond" }
                ),
                CALCULATETABLE (
                    SUMMARIZE ( 'Table 1', 'Table 1'[Candidate ID], 'Table 1'[Status] ),
                    'Table 1'[Status] IN { "Joined" }
                )
            ),
            'Table 1'[Candidate ID]
        )
    ),
    FILTER ( VALUES ( 'Table 1'[Status] ), 'Table 1'[Status] = "Joined" )
)

     

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
4 years ago

VamshiKumar not sure what the final output you are looking for, but here is a shorter version of the measure:

 

Measure 2 = 
VAR __isAbscond = CALCULATE ( COUNTROWS ( Rev ), ALL ( Rev ), VALUES ( Rev[Candidate ID] ), Rev[Status] = "Abscond" )
RETURN IF ( __isAbscond == BLANK (), CALCULATE ( SUM ( Rev[Revenue] ), Rev[Status] = "Joined" ) )

 

✨ Follow us on LinkedIn and subscribe to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡