Calculate() using a filter comparison to another measure

[rpul]

There are similar questions to this (link1, link2), but so far none of the provided solutions have worked. I am trying to create the following measure:

CALCULATE(DISTINCTCOUNT(Table[column1]), FILTER(ALL(Table), Table[column2] > [Measure1]))

where Measure1 is calculated the median of Column2. This measure is excepted from page filters which is why I'm trying to use it as what I'd like is to get a count of the number of rows after a filter is applied where Column2 is greater than the overall median for column2.  Another way to write this measure would be:

CALCULATE(DISTINCTCOUNT(Table[Column1]), Table[Column2] > [Measure1])

Both of these attempts result in an error regarding using True/False expressions as a table filter expression which I don't quite understand given that if I just change the Measure1 in the second measure example to the actual median it works fine. If someone can better explain this error than the documentation that would also be helpful. 

[v-danhe-msft]

Hi @rpul,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[rpul · Accepted solution]

The best I could think to do was to calculate the first measure as a column instead and have it be stable across all rows. This limits the use of the measure as I'd hoped to be able to dynamically change both measures. Below is a sample dataset and more explanation of what I tried to accomplish.

What I wanted was one measure that got the median for a given group or all persons:

CALCULATE(MEDIAN(Table[Stuff]), ALLEXCEPT(Table, Table[Stuff], Table[Group]))

And then to use this measure, given that it's unaffected by filters using 'Name' to calculate the following

CALCULATE(DISTINCTCOUNT(Table[Name]), Table[Stuff] > [previous measure])

Given that measures can't apparently be used as scalar value in a comparison, this isn't possible. So instead I calculate the median for all as a column and gave up on the dynamic part of figuring out the median by group.

[v-danhe-msft]

Hi @rpul,

Based on my test, you could refer to below foumula:

Measure 2 = CALCULATE(DISTINCTCOUNT(Table1[Name]),FILTER('Table1','Table1'[Measure]<MAX('Table1'[Stuff])&&'Table1'[Name]<>MAX('Table1'[Name])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He