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.

 

Candidate IDStatusFeeCTCRevenueDate
1Offered10%1000001000001-01-2021
1Accepted10%1000001000002-01-2021
1Joined10%1000001000003-01-2021
1Abscond10%1000001000004-01-2021
2Offered20%1000002000001-01-2021
2Accepted20%1000002000002-01-2021
2Joined20%1000002000003-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.

 

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

     

     

2 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    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.⚡