DAX Measure for skipping rows based on stages

[VamshiKumar]

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.

Candidate ID	Status	Fee	CTC	Revenue	Date
1	Offered	10%	100000	10000	01-01-2021
1	Accepted	10%	100000	10000	02-01-2021
1	Joined	10%	100000	10000	03-01-2021
1	Abscond	10%	100000	10000	04-01-2021
2	Offered	20%	100000	20000	01-01-2021
2	Accepted	20%	100000	20000	02-01-2021
2	Joined	20%	100000	20000	03-01-2021

I would like to calculate revenue at joined stage of a candidate and if his stage is abscond it shouldnt calculate revenue and sholud return zero. Canidate goes from offered stage to joined stage and we are calculating revenue for joined people but couldnt get logic to exclude people who are absconded.

Thanks in advance. Any help is much appreciated.

[parry2k]

@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.⚡

[smpa01 · Accepted solution]

@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" )
)