DAX Measure with Conditional Filters

[nikhil425]

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

I have a table below like this. 

Operation Date	Operation Type	CashFlow
12/23/2009	Call	-15107386.12
12/23/2009	Return of Excess	302958.48
12/31/2009	Valuation	12232880
10/22/2013	Call	-2653076.64
10/22/2013	Distribution	1359517.24
9/30/2017	Valuation	84501842
10/31/2017	Cash-Adjusted Valuation	84501842
11/30/2017	Cash-Adjusted Valuation	84501842
12/31/2017	Valuation	83147982
1/24/2018	Distribution	998946.54
1/31/2018	Cash-Adjusted Valuation	82149035.46
2/23/2018	Distribution	2042295.89
2/28/2018	Cash-Adjusted Valuation	80106739.57
3/31/2018	Valuation	78814652

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 Date	Operation Type	CashFlow
12/23/2009	Call	-15107386.12
12/23/2009	Return of Excess	302958.48
10/22/2013	Call	-2653076.64
10/22/2013	Distribution	1359517.24
1/24/2018	Distribution	998946.54
2/23/2018	Distribution	2042295.89
3/31/2018	Valuation	78814652

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

Operation Date	Operation Type	CashFlow
12/23/2009	Call	-15107386.12
12/23/2009	Return of Excess	302958.48
10/22/2013	Call	-2653076.64
10/22/2013	Distribution	1359517.24
1/24/2018	Distribution	998946.54
2/23/2018	Distribution	2042295.89
2/28/2018	Cash-Adjusted Valuation	80106739.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

[v-piga-msft · Accepted solution]

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