Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Community!!
i am facing a problem in a DAX measure that i cant solve days now.
I have a sales table which in the first column is the date, in the 2nd column is the customer ID and in the last column i have the quantities sold. My question is: how can i calculate the number of unique customers (Customer ID) sell less than 10 quantities in February of 2018? (in the example below the answer is 1 Customer)
Could you please be so kind to help me??
Date | Customer ID | Quantity |
January | 11 | 5 |
January | 22 | 10 |
January | 11 | 4 |
January | 33 | 7 |
February | 11 | 9 |
February | 55 | 3 |
February | 11 | 2 |
February | 11 | 3 |
March | 77 | 4 |
March | 99 | 7 |
March | 99 | 9 |
March | 77 | 3 |
Thank you!!!
Solved! Go to Solution.
@Anonymous
You can use this MEASURE.
File attached as well
Measure = COUNTX ( FILTER ( VALUES ( Table1[Customer ID] ), CALCULATE ( SUM ( Table1[Quantity] ) ) < 10 ), 1 )
@Anonymous
It depends on how and where you want to show the results
As Daniel pointed out you could use a slicer
Also
1) you can use a VISUAL level filter for Month
2) If you want all other months to show value of February you can use
Measure 2= COUNTX ( CALCULATETABLE ( FILTER ( VALUES ( Table1[Customer ID] ), CALCULATE ( SUM ( Table1[Quantity] ) ) < 10 ), Table1[Date] = "February" ), 1 )
3) If you want all other months to show null you can use
Measure 3 = COUNTX ( CALCULATETABLE ( FILTER ( VALUES ( Table1[Customer ID] ), CALCULATE ( SUM ( Table1[Quantity] ) ) < 10 ), KEEPFILTERS ( Table1[Date] = "February" ) ), 1 )
@Anonymous
You can use this MEASURE.
File attached as well
Measure = COUNTX ( FILTER ( VALUES ( Table1[Customer ID] ), CALCULATE ( SUM ( Table1[Quantity] ) ) < 10 ), 1 )
Thank you very much for the fast response, works great!!
Can we create a measure that shows only the results of February?
Hi @Anonymous,
You could use the slicer to choose the value you want to show:
Regards,
Daniel He
@Anonymous
It depends on how and where you want to show the results
As Daniel pointed out you could use a slicer
Also
1) you can use a VISUAL level filter for Month
2) If you want all other months to show value of February you can use
Measure 2= COUNTX ( CALCULATETABLE ( FILTER ( VALUES ( Table1[Customer ID] ), CALCULATE ( SUM ( Table1[Quantity] ) ) < 10 ), Table1[Date] = "February" ), 1 )
3) If you want all other months to show null you can use
Measure 3 = COUNTX ( CALCULATETABLE ( FILTER ( VALUES ( Table1[Customer ID] ), CALCULATE ( SUM ( Table1[Quantity] ) ) < 10 ), KEEPFILTERS ( Table1[Date] = "February" ) ), 1 )
Thank you very much!! I needed the dax calculation to show only February.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |