Forum Discussion

alialsayer's avatar
alialsayer
Helper I
3 years ago

summarized dax

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/Emocl0eKo4hHilOLYT3KbaYBjI3UhkXolhazGFTRI3jUAw?e=c9Uwjk

 

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  )

 

Need Help
Tutorial Requests

8 Replies

    • alialsayer's avatar
      alialsayer
      Helper I
      3 years ago

      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.

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

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

    • alialsayer's avatar
      alialsayer
      Helper I
      3 years ago

      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's avatar
        v-yinliw-msft
        Community Support
        3 years ago

        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.