topic Re: how to crate dynamic Bucket field in DAX in DAX Commands and Tips

how to crate dynamic Bucket field in DAX

vengadesh_p — Wed, 09 Feb 2022 06:49:54 GMT

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

Re: how to crate dynamic Bucket field in DAX

Samarth_18 — Wed, 09 Feb 2022 07:58:27 GMT

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

Re: how to crate dynamic Bucket field in DAX

vengadesh_p — Wed, 09 Feb 2022 09:24:36 GMT

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

Re: how to crate dynamic Bucket field in DAX

Samarth_18 — Wed, 09 Feb 2022 10:45:48 GMT

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" )

Re: how to crate dynamic Bucket field in DAX

tamerj1 — Wed, 09 Feb 2022 11:59:04 GMT

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

Re: how to crate dynamic Bucket field in DAX

tamerj1 — Thu, 10 Feb 2022 13:46:00 GMT

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!

Re: how to crate dynamic Bucket field in DAX

v-robertq-msft — Mon, 14 Feb 2022 08:46:12 GMT

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.

Re: how to crate dynamic Bucket field in DAX

tamerj1 — Mon, 14 Feb 2022 08:57:04 GMT

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