cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nikhil425 Frequent Visitor
Frequent Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: DAX Measure with Conditional Filters

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.

 

Untitled.png

 

More details, you could refer to the attachment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Highlighted
Community Support Team
Community Support Team

Re: DAX Measure with Conditional Filters

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.

 

Untitled.png

 

More details, you could refer to the attachment.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.