Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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-
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êsMFelix,
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-
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
139 | |
104 | |
104 | |
80 | |
66 |