I have a table that has 2 standard columns (Region, Category) and then 2 measure columns (Yes, other). The measure for the "Yes" column can be seen below...Measure:Availability_Percentage_Yes_Measure = Var Yes = CALCULATE(COUNT('Services'[Category]),FILTER(ALL('Services'),'Services'[Category]=MAX('Services'[Category])&&'Services'[Status]="Yes"))Var Total = CALCULATE(COUNT('Services'[Category]),FILTER(ALL('Services'),'Services'[Category]=MAX('Services'[Category])))returnYes/Total But for some reason the values are all the same for each region, and I cant figure out why. Can someone help?Thanks in advance.

This is because you are using ALL('Services') in your Filter when you calculate the "Yes" variable. What happens is when you calculate the first row for example the initial filter context isRegion = "Amber" Category = "5G in Tariff" But then when you use ALL('Services') this removes the filters on both Region and Category, then in your subsequent filters you are adding back a filter on Category, but you have no filter on Region so you are getting the total for all regions. I think you could probably fix this by simplifying the measure down to the following: Availability_Percentage_Yes_Measure = Var Yes = CALCULATE(COUNT('Services'[Category]),'Services'[Status]="Yes") Var Total = CALCULATE(COUNT('Services'[Category]),ALL('Services')) return Yes/Total One easy way to debug this is to temporarily change the `return` statement in your measure to return `Yes` or `Total` that way you can check that your raw counts are what you expect.

StuartSmith

Power Participant

5 years ago

Solved

Measure on table not filtering.

I have a table that has 2 standard columns (Region, Category) and then 2 measure columns (Yes, other). The measure for the "Yes" column can be seen below...

Measure:

Availability_Percentage_Yes_Measure =

Var Yes = CALCULATE(COUNT('Services'[Category]),FILTER(ALL('Services'),'Services'[Category]=MAX('Services'[Category])&&'Services'[Status]="Yes"))

Var Total = CALCULATE(COUNT('Services'[Category]),FILTER(ALL('Services'),'Services'[Category]=MAX('Services'[Category])))

return

Yes/Total

But for some reason the values are all the same for each region, and I cant figure out why.

Can someone help?

Thanks in advance.

Need Help

d_gosbell
5 years ago
This is because you are using ALL('Services') in your Filter when you calculate the "Yes" variable.

What happens is when you calculate the first row for example the initial filter context is
Region = "Amber"
Category = "5G in Tariff"

But then when you use ALL('Services') this removes the filters on both Region and Category, then in your subsequent filters you are adding back a filter on Category, but you have no filter on Region so you are getting the total for all regions. I think you could probably fix this by simplifying the measure down to the following:

Availability_Percentage_Yes_Measure =

Var Yes = CALCULATE(COUNT('Services'[Category]),'Services'[Status]="Yes")

Var Total = CALCULATE(COUNT('Services'[Category]),ALL('Services'))

return

Yes/Total

One easy way to debug this is to temporarily change the `return` statement in your measure to return `Yes` or `Total` that way you can check that your raw counts are what you expect.

4 Replies

d_gosbell
Super User
5 years ago
This is because you are using ALL('Services') in your Filter when you calculate the "Yes" variable.

What happens is when you calculate the first row for example the initial filter context is
Region = "Amber"
Category = "5G in Tariff"

But then when you use ALL('Services') this removes the filters on both Region and Category, then in your subsequent filters you are adding back a filter on Category, but you have no filter on Region so you are getting the total for all regions. I think you could probably fix this by simplifying the measure down to the following:

Availability_Percentage_Yes_Measure =

Var Yes = CALCULATE(COUNT('Services'[Category]),'Services'[Status]="Yes")

Var Total = CALCULATE(COUNT('Services'[Category]),ALL('Services'))

return

Yes/Total

One easy way to debug this is to temporarily change the `return` statement in your measure to return `Yes` or `Total` that way you can check that your raw counts are what you expect.
- StuartSmith
  Power Participant
  5 years ago
  Thanks, i thought it must have been something to do with the "All", but was unsure how to proceed. I also used your tip about checking the outcome and have identifed another issue. The table I am using the about measure on is a "Reference" table and therefore ony including cells with values in and not all cells such as blank ones and therefore giving an incorrect complered percentage result.
  
  As a side question, although Im going to search now, but is there a way to create a referecne table that includes all rows and not just rows with values in
  Original Table Reference Table
  
  So as a result, i want the completed percentage to be out of all rows and now just completed as that will always 100% (hope that makes sense).
  - v-yingjl
    Community Support
    5 years ago
    Hi StuartSmith ,
    
    If you only want to create a reference table to filter one column not equals blank and retain all columns from the souce table, you can create a calculated table like this:
    
    Reference table = FILTER ( ALL ( 'Table' ), 'Table'[CDR] <> BLANK () )
    
    originalreference
    
    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.