locked
Filter a column which include different variables RRS feed

  • Question

  • Hi,

    I am not so experienced in Dax but i think this is an easy question.

    I have a column 'measure' wich contains different variables.

    How can I create for example a measure FC accuracy which is (ACT/FC)-1.

    FYI: my table is called: MTD

    I tried the following iferror((filter([MTD];[measure]="ACT")/filter([MTD];[measure]="FC"))-1;0)

    Month Country Measure Value
    01.2017 Belgium ACT 100
    01.2017 Japan ACT 100
    01.2017 Italy ACT 50
    01.2017 Belgium FC 80
    01.2017 Japan FC 120
    01.2017 Italy FC 55
    01.2017 Belgium PIPELINE 90
    01.2017 Japan PIPELINE 100
    01.2017 Italy PIPELINE 45

    Thank you

    Tuesday, March 20, 2018 9:45 AM

Answers

  • =calculate(sum('MTD'[Value]);FILTER('MTD';[Measure]="ACT"))

    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, March 20, 2018 2:36 PM

All replies

  • Hi,

    Try this :

    DIVIDE( CALCULATE (SUM(MTD[Value]), FILTER (MTD,MTD[Measure]="ACT"))

    ,

    CALCULATE (SUM(MTD[Value]), FILTER (MTD,MTD[Measure]="FC"))

    ,0)-1


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, March 20, 2018 10:17 AM
  • Hi,

    Thanks for your prompt reply. unfortunaley this does not work...I split up the formula and only tried to create the variable ACT instead of the complete formula by doing the following:

    =calculate(sum('MTD'[Value];FILTER('MTD';[Measure]="ACT"))

    But i also get an error for this: "Expression is not valid or appears to be incomplete. Please review and correct the expression. The end of th einput was reached.Please note that i have to use ; instead of , for the syntax. 

    Tuesday, March 20, 2018 2:18 PM
  • =calculate(sum('MTD'[Value]);FILTER('MTD';[Measure]="ACT"))

    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, March 20, 2018 2:36 PM
  • Hi BusinessAnlyst,

    Thanks for your question.

    Ousama has given a good solution for you. Besides that, you can also try below DAX formula:

    ACT =
    CALCULATE ( SUM ( 'MTD'[Value] ); 'MTD'[Measure] = "ACT" )
    
    FC =
    CALCULATE ( SUM ( 'MTD'[Value] ); 'MTD'[Measure] = "FC" )
    
    FCAccuracy =
    DIVIDE ( [ACT]; [FC] ) - 1

    One more thing, please kindly mark the helpful replies as answers. By doing so, It will benefit all community members who are facing this similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, March 21, 2018 1:58 AM
  • Big thank you to the both of you!You helped me a lot!
    Wednesday, March 21, 2018 1:46 PM