Count Number of Values of a Group within a certain Range

[YupeiChen]

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

[Victormar]

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 🙂

[v-xjiin-msft]

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.

[YupeiChen]

Hi @v-xjiin-msft 

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

[v-xjiin-msft · Accepted solution]

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.

[mariaSam1014]

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?

[YupeiChen]

@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.