summarized dax

[alialsayer]

I just want to calculate how many unique customers have a value per date 

1- less than 2$

2- greater and equal to 2$ and less than 3$

3- greater and equal to 3$ and less than 4$

4- above 4$ 

I might have unique customers who have on the same date multi-value ($) I need to sum up all of them using the DAX measure to count unique customers on each date in the below example Ali should be one unique customer on the 3rd of Jan 2022. 

link : https://zainkwt-my.sharepoint.com/:f:/g/personal/ali_alsayer_kw_zain_com/Emocl0eKo4hHilOLYT3KbaYBjI3...

First, I have to group values by date for each customer, and then I should use the bucket. I will only use the bucket at the end to count how many unique customers are in each bucket. I am trying to find a way to do this in the DAX function instead of grouping values for each customer in a table to avoid performance issues.

the final bucket will be as 

1- less than 2$ ( 2 unique customer has less than 2$ per day )

2- greater and equal to 2$ and less than 3$ ( 1 unique customer has less than 3$ per day )

3- greater and equal to 3$ and less than 4$ ( 1 unique customer has less than 4$ per day )

4- above 4$ ( 3 unique customer has above 4$ per day as peter repeater but we will use count distinct  )

[FreemanZ]

what do you expect? You have already plotted the expected result with table visual.

[alialsayer]

To avoid performance issues, I don't want to group it in Table; instead, I want to group it in Dax measurement. 

[v-yinliw-msft]

Hi alialsayer,

You can try this method:

Measure:

Measure = SUM('Table'[Values($)])

Measure 2 =
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[Values($)] ),
        ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Name] )
    )
RETURN
    SWITCH (
        TRUE (),
        AND ( _sum >= 0, _sum < 2 ), "#DEFFEE",
        AND ( _sum >= 2, _sum < 3 ), "#E044A7",
        AND ( _sum >= 3, _sum < 4 ), "#744EC2",
        _sum >= 4, "#E66C37"
    )

Then in the CONDITIONAL FORMATTING:

There are two ways to color them:

1.

2.

Hope these help you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alialsayer]

This is fantastic; thank you for your prompt response; however, I have a slightly different situation. First, I have to group values by date for each customer, and then I should use the bucket. I am not interested in color formatting as I will only use the bucket at the end to count how many unique customers are in each bucket matting as I will only use the bucket at the end to count how many unique customers are in each bucket. I am trying to find a way to do this in the DAX function instead of grouping values for each customer in a table to avoid performance issues.

[FreemanZ]

then what does the 1/2/3/4 grouping mean? What do you expect?

[alialsayer]

Thank you for your prompt response; however, I have a slightly different situation. First, I have to group values by date for each customer, and then I should use the bucket. I am trying to find a way to do this in the DAX function instead of grouping values for each customer in a table to avoid performance issues.

[amitchandak]

@alialsayer , I thnk you need dynamic segmentation, refer my blog/Video

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[alialsayer]

This is fantastic; thank you for your prompt response; however, I have a slightly different situation. First, I have to group values by date for each customer, and then I should use the bucket. I am trying to find a way to do this in the DAX function instead of grouping values for each customer in a table to avoid performance issues.