Forum Discussion

atavo's avatar
atavo
Helper I
7 years ago
Solved

Please Need DAX Help

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

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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's avatar
    atavo
    7 years ago

    Thanks jdbuchanan

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

     

    Alfred

8 Replies

    • atavo's avatar
      atavo
      Helper I
      7 years ago

      Hi jdbuchanan

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

       

      Not sure how to upload file on this site.

       

      Alfred

      • Cmcmahan's avatar
        Cmcmahan
        Resident Rockstar
        7 years ago

        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.  

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    7 years ago

    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.