Forum Discussion

nikhil425's avatar
nikhil425
Helper I
7 years ago
Solved

DAX Measure with Conditional Filters

Hi, Can anyone help me with the following DAX measure. 

 

I have a table below like this. 

 

Operation DateOperation TypeCashFlow
12/23/2009Call-15107386.12
12/23/2009Return of Excess302958.48
12/31/2009Valuation12232880
10/22/2013Call-2653076.64
10/22/2013Distribution1359517.24
9/30/2017Valuation84501842
10/31/2017Cash-Adjusted Valuation84501842
11/30/2017Cash-Adjusted Valuation84501842
12/31/2017Valuation83147982
1/24/2018Distribution998946.54
1/31/2018Cash-Adjusted Valuation82149035.46
2/23/2018Distribution2042295.89
2/28/2018Cash-Adjusted Valuation80106739.57
3/31/2018Valuation78814652

 

I am using a Operation Date Slicer with before Operand, If I select Operation Data as 3/31/2018, Then I should see only the Max Valuation or Cash Adjusted Valuation and all other operation types. The expected output is below:

 

Operation DateOperation TypeCashFlow
12/23/2009Call-15107386.12
12/23/2009Return of Excess302958.48
10/22/2013Call-2653076.64
10/22/2013Distribution1359517.24
1/24/2018Distribution998946.54
2/23/2018Distribution2042295.89
3/31/2018Valuation78814652

 

If I select 2/28/2018 Then the output should be 

 

Operation DateOperation TypeCashFlow
12/23/2009Call-15107386.12
12/23/2009Return of Excess302958.48
10/22/2013Call-2653076.64
10/22/2013Distribution1359517.24
1/24/2018Distribution998946.54
2/23/2018Distribution2042295.89
2/28/2018Cash-Adjusted Valuation80106739.57

 

I used the below expression to filter out all Valuation and Cash Adjusted Valuation. I should only show the TOP Valuation or Cash Adjusted Valuation based on the date I select. 

 

VIRRCashFlow =
CALCULATE(Sum('Funds_PI - Operations'[Cashflow]),
FILTER('Funds_PI - Operations', 'Funds_PI - Operations'[Operation Date] <= MAX([Operation Date]) && 'Funds_PI - Operations'[Operation Type] <> "Valuation" && 'Funds_PI - Operations'[Operation Type] <> "Cash-Adjusted Valuation"))

 

Please help!

 

Thanks,

Nikhil

 

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi nikhil425,

     

    For your case, please create the measure below and then put it in visual level filter.

     

    Measure 3 =
IF (
    NOT (
        SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Type] )
            IN { "Valuation", "Cash-Adjusted Valuation" }
    )
        && SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Date] )
            <= MAX ( [Operation Date] ),
    "Y",
    IF (
        SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Date] )
            = MAXX (
                ALLSELECTED ( 'Funds_PI - Operations'[Operation Date] ),
                [Operation Date]
            ),
        "Y"
    )
)

     Then you could get the output below.

     

     

    More details, you could refer to the attachment.

     

    Best  Regards,

    Cherry

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi nikhil425,

     

    For your case, please create the measure below and then put it in visual level filter.

     

    Measure 3 =
IF (
    NOT (
        SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Type] )
            IN { "Valuation", "Cash-Adjusted Valuation" }
    )
        && SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Date] )
            <= MAX ( [Operation Date] ),
    "Y",
    IF (
        SELECTEDVALUE ( 'Funds_PI - Operations'[Operation Date] )
            = MAXX (
                ALLSELECTED ( 'Funds_PI - Operations'[Operation Date] ),
                [Operation Date]
            ),
        "Y"
    )
)

     Then you could get the output below.

     

     

    More details, you could refer to the attachment.

     

    Best  Regards,

    Cherry