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

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.

Reply
YupeiChen
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 Group0-11-22-44-88+
BARQ102024131
Control Samples4013000
ECAT Program025075200
FCC Plant4622100

 

Untitled.pngUntitled2.png

1 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 )
    )
)

8.PNG

 

Same to authorize table.

 

Thanks,
Xi Jin.

View solution in original post

6 REPLIES 6
Victormar
Helper IV
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 🙂

v-xjiin-msft
Solution Sage
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.

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:

 

SampleIDCustomer GroupTime2RecieveTime2Auth
111111BARQ8.11.1
111112ECAT Program0.920
111113FCC Plant1.91.9
111114HPC Plant0.16
111115VESPA Plant0.91.1
111116VESPA Plant720


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

 

Customer Group0-11-22-44-88+
BARQ    1
ECAT1    
FCC Plant 1   
HPC Plant1    
VESPA Plant1  1 

 

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

 

Customer Group0-11-22-44-88+
BARQ 1   
ECAT    1
FCC Plant 1   
HPC Plant   1 
VESPA Plant 1  1

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 )
    )
)

8.PNG

 

Same to authorize table.

 

Thanks,
Xi Jin.

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?

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors