Reply
Frequent Visitor
Posts: 6
Registered: ‎10-15-2018
Accepted Solution

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

 


Accepted Solutions
Highlighted
Community Support Team
Posts: 2,720
Registered: ‎02-06-2018

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.

View solution in original post

Attachment

All Replies
Highlighted
Community Support Team
Posts: 2,720
Registered: ‎02-06-2018

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.
Attachment