how to crate dynamic Bucket field in DAX

[vengadesh_p]

hi all, 
i have data like below table, based on that i want to creat calculate colum (Usage Bucket) in DAX

i have Month Start Date & Geo Continent Filter 

Scenario 1: I did not apply any filter 
i want to show this bar chart

Scenario 2: I selected 01/Jan/2021 in Month start date
i want to show this bar chart

Scenario 3: I selected 01/Jan/2021 in Month start date & selected Asia in Geo Continent
i want to show this bar chart

Raw Data

Month Start Date	Geo Continent	User Id	No Time Used
1-Jan-2021	Asia	ID1	5
1-Jan-2021	Europe	ID1	10
1-Feb-2021	Africa	ID1	2
1-Feb-2021	Europe	ID1	3
1-Jan-2021	Asia	ID2	2
1-Jan-2021	Europe	ID2	2
1-Feb-2021	Africa	ID2	3
1-Feb-2021	Europe	ID2	3
1-Jan-2021	Asia	ID3	15
1-Jan-2021	Europe	ID3	15
1-Feb-2021	Africa	ID3	15
1-Feb-2021	Europe	ID3	15
1-Jan-2021	Asia	ID4	1
1-Jan-2021	Europe	ID4	1
1-Feb-2021	Africa	ID4	1
1-Feb-2021	Europe	ID4	1

[v-robertq-msft]

Hi, 

Have you followed the DAX formula posted by tamerj1 to find the solution to your problem?

If so, would you like to mark his reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[tamerj1 · Accepted solution]

Dear @vengadesh_p 

Please refer to the sample solution file here https://www.dropbox.com/t/UTrpT9QkiFfUEQmz

Basically creating a calculated column is not going to help. Therefore, the only solution is to create a measure relaying on a seperate table that contains the "Bucket" names.

Just open the file and have a look at the code which is too long to be posted in a comment here.

This is how the report looks like

If my answer satisfies your requirement, I kindly request you to mark it as Accepted. Thank you

[tamerj1]

Hello @vengadesh_p 
Please let me know if my reply solved your proble. If so, kindly mark it as accepted solution. Kudos are also appreciated. Thank you!

[tamerj1]

Here is the code:

# User = 
VAR CurrentBucket = SELECTEDVALUE (Buckets[Usage Bucket] )
VAR UserTotal =
    SUMMARIZE (
        Data,
        Data[User Id],
        "@TotalPerUser", SUM (Data[No Time Used] )
    )
VAR LessThan5 =
    FILTER ( 
        UserTotal,
        [@TotalPerUser] < 5
    )
VAR LessThan5Count = COUNTROWS ( LessThan5 )
VAR LessThan5Users =
    CONCATENATEX (
        LessThan5,
        Data[User Id],
        ", ",
        Data[User Id], ASC
    )
VAR Between5and9 =
    FILTER ( 
        UserTotal,
        [@TotalPerUser] >= 5 && [@TotalPerUser] < 10
    )
VAR Between5and9Count = COUNTROWS ( Between5and9 )
VAR Between5and9Users =
    CONCATENATEX (
        Between5and9,
        Data[User Id],
        ", ",
        Data[User Id], ASC
    )
VAR Between10and19 =
    FILTER ( 
        UserTotal,
        [@TotalPerUser] >= 10 && [@TotalPerUser] < 20
    )
VAR Between10and19Count = COUNTROWS ( Between10and19 )
VAR Between10and19Users =
    CONCATENATEX (
        Between10and19,
        Data[User Id],
        ", ",
        Data[User Id], ASC
    )
VAR MoreThan20 =
    FILTER ( 
        UserTotal,
        [@TotalPerUser] >= 20
    )
VAR MoreThan20Count = COUNTROWS ( MoreThan20 )
VAR MoreThan20Users =
    CONCATENATEX (
        MoreThan20,
        Data[User Id],
        ", ",
        Data[User Id], ASC
    )
VAR Result =
    SWITCH ( 
        TRUE,
        CurrentBucket = "Less Than 5 Times Used", LessThan5Count & " - " & LessThan5Users,
        CurrentBucket = "5 to 9 Times Used", Between5and9Count & " - " & Between5and9Users,
        CurrentBucket = "10 to 19 Times Used", Between10and19Count & " - " & Between10and19Users,
        MoreThan20Count & " - " & MoreThan20Users
    )
RETURN
    Result

[Samarth_18]

Hi @vengadesh_p ,

You can create a column with below code:-

Column =
VAR times_used =
    CALCULATE (
        SUM ( 'Table (2)'[No Time Used] ),
        FILTER (
            ALL ( 'Table (2)' ),
            'Table (2)'[User Id] = EARLIER ( 'Table (2)'[User Id] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        times_used < 5, "Less than 5 times used",
        times_used >= 5
            && times_used < 10, "5 to 10 times used",
        times_used >= 10
            && times_used < 20, "10 to 20 times used",
        times_used >= 20, "More than 20 time used"
    )

Now add newly create column on table visual and add ID column summerized as distinctcount.

Output:-

Refer file below:-

https://we.tl/t-3ASbNM9A1D 

Thanks,

Samarth

[vengadesh_p]

Thanks you for your responce 

I selected Asia, it show wrong information 

EX: ID2 & ID4 used less than 5 time but it shows wrong result
"Less than 5 times" used should be 2

[Samarth_18]

@vengadesh_p , Try this:-

Column = 
VAR times_used =
    CALCULATE (
        SUM ( 'Table (2)'[No Time Used] ),
        FILTER (
            ALL ( 'Table (2)' ),
            'Table (2)'[User Id] = EARLIER ( 'Table (2)'[User Id] ) &&
            'Table (2)'[Geo Continent] = EARLIER('Table (2)'[Geo Continent])
        )
    )
RETURN
    SWITCH (
        TRUE (),
        times_used < 5, "Less than 5 times used",
        times_used >= 5
            && times_used < 10, "5 to 10 times used",
        times_used >= 10
            && times_used < 20, "10 to 20 times used",
        times_used >= 20, "More than 20 time used"
    )