Forum Discussion

rpul's avatar
rpul
Frequent Visitor
8 years ago
Solved

Calculate() using a filter comparison to another measure

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]),...
Need Help
  • rpul's avatar
    rpul
    8 years ago

    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.

rpul's avatar
rpul
Frequent Visitor
8 years ago

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's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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