Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fanofgolfdsm
Helper I
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.

 

 

LocationCO2_DowntimeUnit_Op_HrsCO2_OMA
Location 1126999.60%
Location 20294100%
Location 3152099.80%
Location 4176399.90%
Location 56114399.50%
Location 68117699.30%
Location 7 1049 
Location 8 605 
Location 9 1046 
Location 10 1016 
Location 11545998.90%
Location 12546498.90%
Location 13 1178 
Location 14 1178 
Location 155117899.60%
Location 16565599.20%
Location 1700100%
Location 180162100%
Location 1901172100%

 

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

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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

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-

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

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-

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.