locked
DAX Measure in Power Pivot RRS feed

  • Question

  • Hi together, short question in the round:

    A table can store three different values ​​in columns awa60 through awa63: 0, 1, or 2. I just want to isolate the value 2 from different columns in a table and a sum over all four columns that contain the value "2" form.

    Here is my measure as example:

    = calculate (count (cca [awa60]), cca [awa60]) = 2+
    calculate (count (cca [awa61]); cca [awa61]) = 2+
    calculate (count (cca [awa62]); cca [awa62] = 2+
    calculate (count (cca [awa63]); cca [awa63]) = 2

    The result should be evaluated in other measures.

    My measure does not work. Can someone help? Greetings Günter

    Wednesday, January 31, 2018 11:47 AM

Answers

  • Hi,

    Your measure should be like this :

    Measure:=CALCULATE(COUNT([awa60]),cca[awa60]=2)+CALCULATE(COUNT([awa61]),cca[awa61]=2)+CALCULATE(COUNT([awa62]),cca[awa62]=2)+CALCULATE(COUNT([awa63]),cca[awa63]=2)


    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]

    Wednesday, January 31, 2018 12:10 PM
  • Hi,

    Thanks for your question.

    Ousama has given a good solution for you. You can also try below DAX formula:
    =
    COUNTROWS ( FILTER ( cca, cca[awa60] ) = 2 )
        + COUNTROWS ( FILTER ( cca, cca[awa61] ) = 2 )
        + COUNTROWS ( FILTER ( cca, cca[awa62] ) = 2 )
        + COUNTROWS ( FILTER ( cca, cca[awa63] ) = 2 )


    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

    Thursday, February 1, 2018 3:07 AM

All replies

  • Hi,

    Your measure should be like this :

    Measure:=CALCULATE(COUNT([awa60]),cca[awa60]=2)+CALCULATE(COUNT([awa61]),cca[awa61]=2)+CALCULATE(COUNT([awa62]),cca[awa62]=2)+CALCULATE(COUNT([awa63]),cca[awa63]=2)


    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]

    Wednesday, January 31, 2018 12:10 PM
  • Hi,

    Thanks for your question.

    Ousama has given a good solution for you. You can also try below DAX formula:
    =
    COUNTROWS ( FILTER ( cca, cca[awa60] ) = 2 )
        + COUNTROWS ( FILTER ( cca, cca[awa61] ) = 2 )
        + COUNTROWS ( FILTER ( cca, cca[awa62] ) = 2 )
        + COUNTROWS ( FILTER ( cca, cca[awa63] ) = 2 )


    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

    Thursday, February 1, 2018 3:07 AM
  • Hi Willson,

    thanks for your Support. Ousamas solution works fine.

    I tried your solution, but it Shows the following error message:

    'Calculation Error in Measure 'Rating': The expression refers to multiple columns. Multiple columns can not be converted to a scalar value.'

    Where is the mistake, exciting.

    Best Regards

    Guenter

    Thursday, February 1, 2018 9:21 AM
  • Hi Ousama, Thank you for your Support, it works fine. Best Regards Guenter
    Thursday, February 1, 2018 12:37 PM
  • Hi,

    You're welcome :)


    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]

    Thursday, February 1, 2018 1:16 PM