cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Count Number of Values of a Group within a certain Range

Hi I am having trouble writing an elegant DAX code to do what I want.

I have a list of customer groups. I want to count the number of times they have a value(s) (Time2Auth/Time2Recieve/TotalTime) within set ranges (0-1, 1-2, 2-4, 4-8, and 8+), all in a new table.

I know I can just add extra columns with 1/0 to identify if they fall into each group, but I'm looking to learn how to better use DAX.

Final Product Example:

 Customer Group 0-1 1-2 2-4 4-8 8+ BARQ 10 20 24 13 1 Control Samples 40 13 0 0 0 ECAT Program 0 2 50 75 200 FCC Plant 46 22 1 0 0

1 ACCEPTED SOLUTION
Solution Sage

Hi @YupeiChen,

Check this:

New calculated table with SUMMARIZE():

```Recieve table =
SUMMARIZE (
TestTable,
TestTable[Customer Group],
"0-1", CALCULATE (
COUNT ( TestTable[Time2Recieve] ),
FILTER (
TestTable,
TestTable[Time2Recieve] > 0
&& TestTable[Time2Recieve] <= 1
)
),
"1-2", CALCULATE (
COUNT ( TestTable[Time2Recieve] ),
FILTER (
TestTable,
TestTable[Time2Recieve] > 1
&& TestTable[Time2Recieve] <= 2
)
),
"2-4", CALCULATE (
COUNT ( TestTable[Time2Recieve] ),
FILTER (
TestTable,
TestTable[Time2Recieve] > 2
&& TestTable[Time2Recieve] <= 4
)
),
"4-8", CALCULATE (
COUNT ( TestTable[Time2Recieve] ),
FILTER (
TestTable,
TestTable[Time2Recieve] > 4
&& TestTable[Time2Recieve] <= 8
)
),
"8+", CALCULATE (
COUNT ( TestTable[Time2Recieve] ),
FILTER ( TestTable, TestTable[Time2Recieve] > 8 )
)
)```

Same to authorize table.

Thanks,
Xi Jin.

6 REPLIES 6
Helper IV

Hi all,

Is there any way to then plot the result? I was trying to do it, but then I wanted to visualize the data then in a histogram fashion, but as we are creating different columns, it is not allowed.

Thanks for any input 🙂

Solution Sage

Hi @YupeiChen,

=> I want to count the number of times they have a value(s) (Time2Auth/Time2Recieve/TotalTime) within set ranges (0-1, 1-2, 2-4, 4-8, and 8+), all in a new table.

What's the logic to define the ranges? For example, let's see customer group is BARQ. Why 10 is under the range 0-1 and 13 is under 4-8 and so on? What are these logic?

Could you please share us some sample data which we can copy and paste directly? And its corresponding expected result. So that we can get a right direction and make some proper tests. With current information, it is hard for us to understand your requirement.

Thanks,
Xi Jin.

Frequent Visitor

I will make up some sample data for now, and will supplement with actual data when I return to work.

Time2Recieve is calculated by recieved timestamp - login timestamp (not shown).

Time2Auth is calculated by authorized timestamp - recieved timestamp (not shown).

Essentially we are trying to track how long it takes a plant operator to bring the lab a sample (Time2Recieve), and then how long it takes the lab technicians to run the sample (Time2Auth).

For 0-1, logic is 0<Time<=1

For 1-2, logic is 1<Time<=2

For 2-4, logic is 2<Time<=4

For 4-8, logic is 4<Time<=8

For 8+,  logic is 8<Time

Sample Input Data:

 SampleID Customer Group Time2Recieve Time2Auth 111111 BARQ 8.1 1.1 111112 ECAT Program 0.9 20 111113 FCC Plant 1.9 1.9 111114 HPC Plant 0.1 6 111115 VESPA Plant 0.9 1.1 111116 VESPA Plant 7 20

Sample Table to track how long it takes to recieve samples from customer groups, based on above data

 Customer Group 0-1 1-2 2-4 4-8 8+ BARQ 1 ECAT 1 FCC Plant 1 HPC Plant 1 VESPA Plant 1 1

Sample Table to track how long it takes to authorize samples from customer groups, based on above data

 Customer Group 0-1 1-2 2-4 4-8 8+ BARQ 1 ECAT 1 FCC Plant 1 HPC Plant 1 VESPA Plant 1 1
Solution Sage

Hi @YupeiChen,

Check this:

New calculated table with SUMMARIZE():

```Recieve table =
SUMMARIZE (
TestTable,
TestTable[Customer Group],
"0-1", CALCULATE (
COUNT ( TestTable[Time2Recieve] ),
FILTER (
TestTable,
TestTable[Time2Recieve] > 0
&& TestTable[Time2Recieve] <= 1
)
),
"1-2", CALCULATE (
COUNT ( TestTable[Time2Recieve] ),
FILTER (
TestTable,
TestTable[Time2Recieve] > 1
&& TestTable[Time2Recieve] <= 2
)
),
"2-4", CALCULATE (
COUNT ( TestTable[Time2Recieve] ),
FILTER (
TestTable,
TestTable[Time2Recieve] > 2
&& TestTable[Time2Recieve] <= 4
)
),
"4-8", CALCULATE (
COUNT ( TestTable[Time2Recieve] ),
FILTER (
TestTable,
TestTable[Time2Recieve] > 4
&& TestTable[Time2Recieve] <= 8
)
),
"8+", CALCULATE (
COUNT ( TestTable[Time2Recieve] ),
FILTER ( TestTable, TestTable[Time2Recieve] > 8 )
)
)```

Same to authorize table.

Thanks,
Xi Jin.

Helper I

I have got a similar case but my

`COUNT ( TestTable[Time2Recieve] )`

TestTable[Time2Receive]) is a measure not a column so is this possible i can make count of values of measure and put them in a range table?

Frequent Visitor

@v-xjiin-msft

Brilliant!

Thank you very much, looking at the way you organize the code was very informative!

I was previously aware of calculate, count, filter, and how to make a table with columns, but could not put it all together correctly with syntax.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.