Please Need DAX Help

[atavo]

Example

Hi trying to understand why my measure is not working the way it should. I have the following measures:

1. Amount: = SUM(Data[New Funds])

2. DecisionAmt: = CALCULATE([Amount],

                              USERELATIONSHIP (Calendar[Date],Data[Decision Date]))

3. DecisionAmt Declined: = CALCULATE([DecisionAmt],

                                             FILTER(

                                             VALUES(Data[Report Type]),

                                             Data[Report Type]="declined"

                                             ))

I have a table with Year-Month on the row with [DecisionAmt] and [DecisionAmt Declined] measures. Also, I have a slicer on Data[Report Type] column and I have sliced by "declined".

I had expected [DecisionAmt Declined] to show declined data for 2019-06 but it does not. Is this a bug or is my measure(i.e. [DecisionAmt Declined]) wrong.

Alfred

[jdbuchanan71 · Accepted solution]

Very odd, somehow the FILTER in your measure was interacting with the context to exclude a row although I am not sure why.

This will return the correct amount for you:

DecisionAmt Declined = 
CALCULATE(
    [DecisionAmt],
    Data[Report Type]="declined"
)

[v-diye-msft]

Hi ,

Actually I don't think it's needed to add the third measure if you just wanna the [DecisionAmt] changing by slicer, every time you click the slicer, the data will be filtered by specific type. 

But I believe the suggestion provided by @jdbuchanan71   does work. 

[jdbuchanan71]

Hello @atavo 

That is odd, any chance you can share your .pbix file?

[atavo]

Hi jdbuchanan

Can do. Any chance I can get your email contact?

Not sure how to upload file on this site.

Alfred

[Cmcmahan]

I would be interested in seeing your .pbix too.  This is a super weird issue.  I'm curious what happens if you change the third measure to this: 

DecisionAmt Declined: = CALCULATE([DecisionAmt],
    FILTER(
        Data,
        Data[Report Type]="declined"
))

The best way to share .pbix files on this forum is to upload it to Google Drive, Dropbox, OneDrive, etc. and post the shareable link.  

[atavo]

https://www.dropbox.com/s/k7fzwypwgknvulf/TestvLosity.pbix?dl=0

[jdbuchanan71 · Accepted solution]

Very odd, somehow the FILTER in your measure was interacting with the context to exclude a row although I am not sure why.

This will return the correct amount for you:

DecisionAmt Declined = 
CALCULATE(
    [DecisionAmt],
    Data[Report Type]="declined"
)

[atavo · Accepted solution]

Thanks jdbuchanan

Would be interesting to know why DAX is behaving this way.

Alfred

[v-diye-msft]

Hi @atavo 

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. others will benefit from here.