Forum Discussion

LoganFFS's avatar
LoganFFS
Frequent Visitor
1 year ago
Solved

DAX Measure

I have a measured column in a data table with the following code:

 

ClientCategorySum = 

        SUM('Trades'(adjustedgrossamount)),

        FILTER(

                  ALLSELECTED('Trades'),

                  'Trades'(ClientLastName) = SELECTEDVALUE('Trades'(ClientLastName))&&

                  'Trades'(Category) = SELECTEDVALUE('Trades'(Category))

      )

)

What it does is returns a value in the column that has a sum by category. I want to filter the dataset by this measured column with the options of + or - sums, but a measure cant be put into a slicer and a calculated column wont sort properly by the other filters I have on the dashboard. If anyone has any workaround suggestions I would appreciate it greatly. Thanks in advance!

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi LoganFFS ,

     

    I used some sample data on my side and tried to reproduce the issue, but it's working fine for me. I used following measures.

    ClientCategorySum = 
CALCULATE(
    SUM('Trades'[adjustedgrossamount]),
    FILTER(
        ALLSELECTED('Trades'),
        'Trades'[ClientLastName] = SELECTEDVALUE('Trades'[ClientLastName]) &&
        'Trades'[Category] = SELECTEDVALUE('Trades'[Category])
    )
)

    ClientCategorySumSignFilter = 
VAR SelectedSign = SELECTEDVALUE(MeasureFilter[Sign])
VAR ClientSum =
    CALCULATE(
        SUM('Trades'[adjustedgrossamount]),
        FILTER(
            ALLSELECTED('Trades'),
            'Trades'[ClientLastName] = SELECTEDVALUE('Trades'[ClientLastName]) &&
            'Trades'[Category] = SELECTEDVALUE('Trades'[Category])
        )
    )
RETURN
    SWITCH(
        SelectedSign,
        "Positive", IF(ClientSum > 0, 1, 0),
        "Negative", IF(ClientSum < 0, 1, 0),
        1
    )

    expected output: 

    Hope this helps. Please reach out for further assistance.

     

    Thank you.

     

8 Replies

  • maruthisp's avatar
    maruthisp
    Super User
    1 year ago

    Hi LoganFFS,

    DAX Measure - LoganFFS.pbix

    Please check the above pbix file. Hope this help to solve the problem.

    Please let me know if you have any further questions or need clarifications.

     

    If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

     

    Best Regards, 

    Maruthi 

    LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

    X            -  Maruthi Siva Prasad - (@MaruthiSP) / X



    • LoganFFS's avatar
      LoganFFS
      Frequent Visitor
      1 year ago

      Hi there, sorry for my absence from the forum here. I am not authorized to open a Pbix file on my computer here so is there a way you can provide the measures code written in the response? If you are able to do that it would be hugely appreciated. Thanks and sorry once again for my absence. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi LoganFFS ,

    Thank you for reaching out to the Microsoft fabric community forum. Thank you maruthisp for your inputs on this issue.

    After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used sample data on my end and successfully implemented it.     

    I am including .pbix file for your better understanding, please have a look into it.

    Hope this helps. Please reach out for further assistance.

     

    Thank you.

    • LoganFFS's avatar
      LoganFFS
      Frequent Visitor
      1 year ago

      Hi there, sorry for my absence from the forum here. I am not authorized to open a Pbix file on my computer here so is there a way you can provide the measures code written in the response? If you are able to do that it would be hugely appreciated. Thanks and sorry once again for my absence. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi LoganFFS ,

         

        I used some sample data on my side and tried to reproduce the issue, but it's working fine for me. I used following measures.

        ClientCategorySum = 
CALCULATE(
    SUM('Trades'[adjustedgrossamount]),
    FILTER(
        ALLSELECTED('Trades'),
        'Trades'[ClientLastName] = SELECTEDVALUE('Trades'[ClientLastName]) &&
        'Trades'[Category] = SELECTEDVALUE('Trades'[Category])
    )
)

        ClientCategorySumSignFilter = 
VAR SelectedSign = SELECTEDVALUE(MeasureFilter[Sign])
VAR ClientSum =
    CALCULATE(
        SUM('Trades'[adjustedgrossamount]),
        FILTER(
            ALLSELECTED('Trades'),
            'Trades'[ClientLastName] = SELECTEDVALUE('Trades'[ClientLastName]) &&
            'Trades'[Category] = SELECTEDVALUE('Trades'[Category])
        )
    )
RETURN
    SWITCH(
        SelectedSign,
        "Positive", IF(ClientSum > 0, 1, 0),
        "Negative", IF(ClientSum < 0, 1, 0),
        1
    )

        expected output: 

        Hope this helps. Please reach out for further assistance.

         

        Thank you.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi LoganFFS ,

     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

     

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi @LoganFFS ,

     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

     

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi LoganFFS ,

     

    We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
    If you still require support, please let us know, we are happy to assist you.

     

    Thank you.