Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to 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.
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 🙂
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.
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 |
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.
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?
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
59 | |
58 |