COUNT on DAX measure

[atul__1984]

Hi,

I have list of retailers. All respective column have been created with the help of DAX Measures.  Now my challenge is that I need to find COUNT OF RETAILERS falling in each slab.  Below are measures for each value

First Date = CALCULATE(MIN(Calendar_Lookup[Date]),ALLSELECTED(Calendar_Lookup[Date]))

Last Date = CALCULATE(MAX(Calendar_Lookup[Date]),ALLSELECTED(Calendar_Lookup[Date]))

Count of Days = CALCULATE(COUNT(Calendar_Lookup[Date]), DATESBETWEEN(Calendar_Lookup[Date],[First Date],[Last Date]))

Invoice Days = IFERROR(CALCULATE(DISTINCTCOUNT(Invoice[invoice_date]), FILTER(Invoice,Invoice[retailerId])),0)

Invoice Days % = IFERROR(Invoice[Invoice Days] / [Count of Days], 0)

Invoice Day % Slab =
IF(Invoice[Invoice Days %] = 0 || ISBLANK(Invoice[Invoice Days %]) ,"0%",
IF(Invoice[Invoice Days %] > 0 && Invoice[Invoice Days %] <= 0.1,"1% - 10%",
IF(Invoice[Invoice Days %] > 0.1 && Invoice[Invoice Days %] <= 0.2, "11% - 20%",
If (Invoice[Invoice Days %] > 0.2 && Invoice[Invoice Days %] <= 0.35, "21% - 35%",
If (Invoice[Invoice Days %] > 0.35 && Invoice[Invoice Days %] <= 0.5, "35% - 50%",
If (Invoice[Invoice Days %] > 0.5 && Invoice[Invoice Days %] <= 0.75, "51% - 75%", "76% - 100%"))))))

I can not not use COUNT on a DAX Measure since it works on Column only.  

Retailer Name	Count of Days	Invoice Days	Invoice Days %	Invoice Day % Slab
A1	48	42	88%	76% - 100%
A2	48	38	79%	76% - 100%
A3	48	29	60%	51% - 75%
A4	48	28	58%	51% - 75%
A5	48	27	56%	51% - 75%
A6	48	26	54%	51% - 75%
A7	48	26	54%	51% - 75%
A8	48	25	52%	51% - 75%
A9	48	23	48%	35% - 50%
A10	48	23	48%	35% - 50%

I want output like below. 

Invoice Day % Slab	Count
0%	5
1% - 10%	982
11% - 20%	66
21% - 35%	115
35% - 50%	32
51% - 75%	6
76% - 100%	2

Second challenge is that DAX calculations are very slow and I have ever growing list. If there is any other more efficient way, please advise.

Thanks & Best Regards

[jthomson]

---

@atul__1984 wrote:

Second challenge is that DAX calculations are very slow and I have ever growing list. If there is any other more efficient way, please advise.

Thanks & Best Regards

---

I wouldn't have necessarily thought this would improve speed, but it would make your statements more user friendly, at least to read:

Assuming [Invoice Days %] can't be negative, then everything I've crossed out in pink is superfluous, as every single entry would hit that condition at that stage of the statement anyway. Second line I've pinked through for example - you've already chosen everything that's between 0% and 10%, telling the measure to check it's over 10% is redundant

[amitchandak]

@atul__1984 , refer if these links can help

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization