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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

5 REPLIES 5
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.

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.