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

     

     

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