cancel
Showing results for
Did you mean:
Helper I

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

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
Super User

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

Helper I

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

Microsoft

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

Helper I

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-

Microsoft

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

Helper I

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

#### Microsoft Build is May 24-26. Have you registered yet?

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

#### Charticulator Design Challenge

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

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.