Forum Discussion

RobG's avatar
RobG
Frequent Visitor
7 years ago

ALLEXCEPT in a Measure vs Calculated Column

I'm really hoping someone can help me here.  I want to create a dynamic chart based on a count of products during a specific time that shows me the top 5 and puts the rest into an "Others" category.   I can successfully make a measure that shows this but I can't use that measure in a chart legend.   When I use the same code in a calculated column, the rankings come out differently.   It seems to be grouping the dates in a different way but I can't figure out a way around it.   

 

The following measures work:  

 

Product Rank = RANKX(ALLSELECTED(Contacts[Product]), CALCULATE(COUNT(Contacts[Product]), ALLEXCEPT(Contacts, Contacts[Product], Contacts[Entered])))
 
Product Chart = IF([Product Rank] <= 5, SELECTEDVALUE(Contacts[Product]), "Others")
 
On the left is the results I get using the measures above.   When I try to rank using a calculated column (Right), I'm getting completely different results that seem to be the way it is grouping the data based on the dates selected.  
 
 
 
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Your exactly right.

     

    A calculated column is process (calculated) when you refresh the data into the data model. After that the data is static, it wont change regardless of how you filter your report.  As a result the ranking is operating over all the data in your table.

     

    A measure on the other hand calculates at run time (when you interact with the report) .  In this the scenerio it takes into account the filters you have on your dashboard as well as any filters you apply in your measure itself.  As a result it will take into account the data filters that you have selected.

     

    • RobG's avatar
      RobG
      Frequent Visitor
      7 years ago

      So is there any way to take the "Product Chart" measure in the left most table and use that as a legend or axis on a chart that changes depending on the dates selected?