Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Count the number of times a measure is less th...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Count the number of times a measure is less than a user specified value

02-20-2017
06:06 AM

I am using a few measures to calculate some values from a table that contains hourly data. A sample of a matrix table with the measures I am using is listed below.

Location | CO2_Downtime | Unit_Op_Hrs | CO2_OMA |

Location 1 | 1 | 269 | 99.60% |

Location 2 | 0 | 294 | 100% |

Location 3 | 1 | 520 | 99.80% |

Location 4 | 1 | 763 | 99.90% |

Location 5 | 6 | 1143 | 99.50% |

Location 6 | 8 | 1176 | 99.30% |

Location 7 | 1049 | ||

Location 8 | 605 | ||

Location 9 | 1046 | ||

Location 10 | 1016 | ||

Location 11 | 5 | 459 | 98.90% |

Location 12 | 5 | 464 | 98.90% |

Location 13 | 1178 | ||

Location 14 | 1178 | ||

Location 15 | 5 | 1178 | 99.60% |

Location 16 | 5 | 655 | 99.20% |

Location 17 | 0 | 0 | 100% |

Location 18 | 0 | 162 | 100% |

Location 19 | 0 | 1172 | 100% |

The matrix table is made up of the following Measures:

CO2_Downtime = CALCULATE(SUM('Downtime'[Summed Values]),

FILTER(ALL('Downtime'[Data Point Name]),

('Downtime'[Data Point Name]) = "CO2 Downtime (hr)"))

Unit_Op_Hrs = CALCULATE(SUM('Downtime'[Other Attributes.DPV Operating Ind]),

FILTER(ALL('Downtime'[Data Point Name]),

('Downtime'[Data Point Name]) = "Unit Operating Time"))

CO2_PMA = IF(CONTAINS('Quarterly PMA - Pollutant Table','Quarterly PMA - Pollutant Table'[CO2],"Yes"),

1-DIVIDE('Downtime'[CO2_Downtime],'Downtime'[Unit_Op_Hrs]))

The main thing to note here is that CO2_PMA is based off of the other two measures as well as another table to see if it should be calculated for a specific location. What I would like to do is to count the number of times that CO2_PMA is less than 99%. I’ve tried multiple different suggestions from this forum but none of the seem to work for my situation. Ideally I'd like to roll this up into Card so that I can show the total number of a values < 99%. In this case, the card would show a value of 2.

Any suggestions are appreciated.

Thanks,

-Scott-

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-20-2017
07:58 AM

Hi @fanofgolfdsm,

The measures are dinamic and according to the values that you have in the visuals, they calculate according to the information you see.

What I did to calculate this was to transform the CO2_PMA into a column and then calculate a measure based on that to use on the card,

Count_PMA = CALCULATE(COUNT(Table1[Location]),Table1[CO2_PMA]<0,99)

I don't know the full extend of you data and if you are able to have this in a column, to help you further are you downtime and unit hours several records per location or are they as you show them?

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-20-2017
09:18 AM

MFelix,

I tried your solution with the data I provided and had to change COUNT to COUNTA for it to work in a visual. I believe that this is because the Location field is TEXT.

When I tried to modify this equation to work with my actual table, I get the following error while trying to save the measure:

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

I've run into this a couple of times but haven't been able to wrap my mind around what it means (after lots of Google searches). The only thing I can think of is that I did filter out some of the Locations while writing my query. Is this the cause or is there some other guidance you can provide?

This stuff is still a little new to me.

Thanks,

FanofgolfDSM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-20-2017
11:04 PM

Hi @fanofgolfdsm,

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

The syntax of CALCULATE function is CALCULATE(<expression>,<filter1>,<filter2>…)

The filter "Table1[CO2_PMA]<0,99" returns Ture/False, which is not recoginzed by CALCULATE function. Please modify it as the follows.

Count_PMA = CALCULATE(COUNTA(Table1[Location]),FILTER(Table1,Table1[CO2_PMA]<0.99))

Please use the formula and check if it still have the issue.

If you have other issues, please let me know.

Best Regards,

Angelia

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-21-2017
05:04 AM

Thanks Angelia, your equation got rid of that error but the count is still not correct. I believe it has to do with the underlying data.

Instead of getting a single count (1 or 0) for each location, I'm getting a count of all the rows that match the filter in the table. If you look at the table I posted, that is actually a visual in Power BI using the detailed measures. The Unit_Op_Hrs column gives you an idea as to the number of records underneath. Take Location 6 for example. It has 1176 Unit_Op_Hrs which means there are at least 1176 data points that make up the CO2_PMA value.

Location 6 PMA Calculation Desired

CO2_PMA = 1 - (CO2_Downtime/Unit_Op_Hrs) = 1 - (8 / 1176) = 0.993

Instead of counting each of the hours (this is all time based hourly data for 2017) with a CO2_PMA less than 99%, I only want to count the CO2_PMA aggregate values that are less than 99%. I should only have a single value (1 or 0) for the plant.

Note that the total number of hours to date for each location is 1228. This means that the CO2_PMA for each location will be an aggregate of up to 1228 data points. Instead of showing a value of 2 for the number of locations with less than 99% PMA, I'm showing 23,000+.

Hopefully this make some sense.

Thanks,

-Scott-

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-28-2017
05:42 PM

Hi @fanofgolfdsm,

Your given table is a matrix table you want to get, or the resource data? I am confusing what the resource table looks like.

Best Regards,

Angelia

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-09-2017
06:44 AM

I am calculating the data using measures and the putting those values in a matrix table. I want to show a CO2_PMA values for each location and be able to count the number of locations that have a CO2_PMA less than a specific value. The CO2_PMA data is not stored in a source table but is calculated on the fly.

I was speaking to another person at my company and he thougth that the SUMMARIZECOLUMNS function might work better. It would allow the PMA data to be stored in a table and this might also have the benefit of making it easier to do the count I want. Unfortunately, I have not been able to try this out yet.

Thanks,

-Scott-

Announcements

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Featured Topics

Top Solution Authors

User | Count |
---|---|

352 | |

99 | |

62 | |

50 | |

49 |

Top Kudoed Authors

User | Count |
---|---|

353 | |

122 | |

84 | |

68 | |

62 |