Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure needing different context

Dear community,    I would appreciate some help in the following issue. I have aggregated data about some stores. I have several more columns describing the stores but, for the sake of the example...
General Comment
  • m3tr01d's avatar
    m3tr01d
    5 years ago

    You could change your calculation like this :

    CALCULATE(
    DISTINCTCOUNT('Table'[Store]),
    FILTER(
        ALL( 'Table'[Store] ),
        [measureA] > 0.05
    )
)

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello Greg_Deckler 

 

Thanks for your reply, I have spent a lot of time and I am sorry but I am not figuring this out. I cannot see how to combine HASONEFILTER() and maybe SUMMARIZE() to get both results I want. 

 

In fact, my attempts were working in the following direction:

For the first question:

measureB = 
CALCULATE(
    DISTINCTCOUNT('Table'[Store]),
    ALL('Table'[Store]),
    [measureA] < 0.05
)

But I get the error:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed 

 

For the second question no idea...

 

Regards,

v-kkf-msft's avatar
v-kkf-msft
Community Support
5 years ago

Hi Anonymous ,

 

For the first question, try the following formula:

 

MeasureB = IF( MAXX( ALLEXCEPT( 'Table', 'Table'[Year] ), [MeasureA] ) > 0.05, 1 )

 

And for the second question, try the formula provided by m3tr01d.

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Dear v-kkf-msft ,

     

    Thank you for your answer. I am afraid the solution you propose appears to be correct but it is not. I have the impression the result is correct "by chance", and it is something I had observed before:

     

    If I change slightly your measure to:

    MeasureB-2 = MAXX( ALLEXCEPT( 'Table', 'Table'[Year] ), [MeasureA] )

    just to check what Power BI is computing in this MAXX, I do not get what I expect:

     

     

    The result for the last column should be (0.06, 0.06, 0.17). After doing some tests, I guess that what Power BI is computing under the hood is [measureA] NOT in the current context, but for each row of the original Table, and then computing the maximum of all the rows (in this case, the maximum IS INDEED computed given the current context).

     

     

    After reading a bit about it, I came to the conclusion that iterator functions like MAXX() "get rid" of all the context and compute all the expressions row by row.

    Of course when you compute [measureA] not in the current context but just row by row, you get just a simple division like I show in the Excel capture in column Perf/Amount. But this is wrongly computed.

     

    As a matter of fact by the way, I believe it should be:

    ALL('Table'[Store])

    instead of 

    ALLEXCEPT( 'Table', 'Table'[Year] )

     

    Don't you think so?

     

    m3tr01d formula solved the problem for question 1:

     

    CALCULATE(
    DISTINCTCOUNT('Table'[Store]),
    FILTER(
        ALL( 'Table'[Store] ),
        [measureA] > 0.05
    )
)

     

    But question 2 remains unsolved (which unfortunately is the final table in my report...).

     

    Best wishes,