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
PaulHallam
Helper II
Helper II

Can i count values within a measure?

Hi I am new to the forum so apologise if this is a little garbled.

Basically I need to know the count of values in different ranges within a measure….i think.

I have a data table as below and have added a calculated column to classify if the sensor was used during each specific day (>6 readings).

DateCode08:00:0008:10:0008:20:0008:30:0008:40:0008:50:0009:00:0009:10:0009:20:0009:30:0009:40:0009:50:0010:00:00Occupied for Day
01/04/19Sensor110011000010110
01/04/19Sensor210111100001000
01/04/19Sensor300001010001110
01/04/19Sensor411100011001111
01/04/19Sensor501001010101111
01/04/19Sensor610010010001110
01/04/19Sensor700101000111100
01/04/19Sensor800001111101111
01/04/19Sensor900000000101110
02/04/19Sensor110111010011101
02/04/19Sensor210100001100110
02/04/19Sensor311110010000000
02/04/19Sensor401011000111000
02/04/19Sensor500011101100100
02/04/19Sensor611101000110000
02/04/19Sensor700000100100010
02/04/19Sensor800011010100010
02/04/19Sensor900110111111101
03/04/19Sensor100100011101010
03/04/19Sensor200011000110000
03/04/19Sensor311110010100101
03/04/19Sensor410001110011000
03/04/19Sensor501010010110010
03/04/19Sensor610100001011000
03/04/19Sensor701100000111111
03/04/19Sensor810011101011111
03/04/19Sensor901100000011010

In this example Sensor 1 is occupied for one day, with an occupied day’s percentage of 33.3% (1 out of 3 days).

I can produce a measure to count occupied days per sensor and a measure to calculate a sensors occupied percentage over a date range and populate a table visualisation.

CodeOccupied for DayOccupied %
Sensor1133.3%
Sensor200.0%
Sensor3133.3%
Sensor4133.3%
Sensor5133.3%
Sensor600.0%
Sensor7133.3%
Sensor8266.7%
Sensor9133.3%

I need a measure to count the number of sensors that were occupied in the following ranges 0%-20%, 20%-40%, 40%-60%, 60%-80%, 80%-100%.

All data is subject to a date range slicer on the report page.

I have only shown a short data set there are over 10,000 sensors with over a years’ worth of data

Please help.

1 ACCEPTED SOLUTION

Hi @PaulHallam ,

Please create a table with decimal value of range start and end, then you can use following measure to summarize and look correspond records and get count of them.

18.png

Measure formula:

 

Summary Count =
VAR temp =
    SUMMARIZE (
        'Sample',
        [Sensor Number],
        "Count", [Occupied Days],
        "Percent", [Occupied %]
    )
RETURN
    COUNTROWS (
        FILTER ( temp, [Percent] >= MAX ( T4[Start] ) && [Percent] <= MAX ( T4[End] ) )
    ) + 0

19.png

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

Hi @PaulHallam ,

I'd like to suggest you do unpivot columns on your time fields to convert them to time attribute and value.

Unpivot columns (Power Query)

Then you can write condition based on time value instead hard code all time fields and compare with different time fields.

BTW, you can't direct aggregate with measure result. Since measure result are dynamic calculated based on its row contents. So you need to create variable summarized table to restore scenario, then you can do aggregate with this summary table.

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for the advice Xiaoxin. I had already tried this and got as far as the following table 'SUMMARY' but then got stuck;

DateSensorOccupied for Day
01/01/2019S11
01/01/2019S21
01/01/2019S31
01/01/2019S41
01/01/2019S51
02/01/2019S11
02/01/2019S21
02/01/2019S31
02/01/2019S40
02/01/2019S51
03/01/2019S10
03/01/2019S20
….….….

 

In the above table sensor 1  was used on 2 out of 3 days, and i can easily calculate individual percentage use in a measure.

% Use = 'SUMMARY' [Occupied for day]/DISTINCTCOUNT('SUMMARY'[Date])
I need a measure to tell me the number of sensors that are in the ranges 0%-20%, 20%-40%, 40%-60%, 60%-80% and 80%-100%.
I am assuming i have to add a table with the ranges in but cant seem to work it out
Any thoughts, i am pretty new to Power BI so please explain logic for a solution in detail if possible,

Thanks Paul

Hi @PaulHallam ,

Can you please share some sample data for test? Please do mask on sensitive data before share.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin,

The 'DATA' table below contains data for 5 sensors over 6 days

DateSensor NumberOccupied
01/01/2019Sensor 11
01/01/2019Sensor 20
01/01/2019Sensor 31
01/01/2019Sensor 40
01/01/2019Sensor 51
02/01/2019Sensor 10
02/01/2019Sensor 20
02/01/2019Sensor 30
02/01/2019Sensor 40
02/01/2019Sensor 51
03/01/2019Sensor 11
03/01/2019Sensor 21
03/01/2019Sensor 30
03/01/2019Sensor 40
03/01/2019Sensor 51
04/01/2019Sensor 10
04/01/2019Sensor 21
04/01/2019Sensor 30
04/01/2019Sensor 41
04/01/2019Sensor 51
05/01/2019Sensor 10
05/01/2019Sensor 21
05/01/2019Sensor 30
05/01/2019Sensor 41
05/01/2019Sensor 50
06/01/2019Sensor 10
06/01/2019Sensor 21
06/01/2019Sensor 30
06/01/2019Sensor 41
06/01/2019Sensor 50

 

I have two measures;


     Occupied Days = SUM('DATA' [Occupied])
     Occupied % = DATA[Occupied Days]/DISTINCTCOUNT('DATA' [Date])

 

From these i can produce a table as below. This table works with the date slicer on the report 

Sensor NumberOccupied DaysOccupied %
Sensor 1233.33%
Sensor 2466.67%
Sensor 3116.67%
Sensor 4350.00%
Sensor 5466.67%

 

What i am trying to do is generate the table below that also works with the date slicer, but i can't seem to get close and i cant find any solutions in the forum. Please help if possible.

 

RangeCount
0%-20%1
20%-40%1
40%-60%1
60%-80%2
80%-100%0


Thanks

Hi @PaulHallam ,

Please create a table with decimal value of range start and end, then you can use following measure to summarize and look correspond records and get count of them.

18.png

Measure formula:

 

Summary Count =
VAR temp =
    SUMMARIZE (
        'Sample',
        [Sensor Number],
        "Count", [Occupied Days],
        "Percent", [Occupied %]
    )
RETURN
    COUNTROWS (
        FILTER ( temp, [Percent] >= MAX ( T4[Start] ) && [Percent] <= MAX ( T4[End] ) )
    ) + 0

19.png

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi 

 

The axis are the other way round. My mistake

All working great thanks for your help...!

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.