Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
vengadesh_p
Helper I
Helper I

how to crate dynamic Bucket field in DAX

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

1.png

i have Month Start Date & Geo Continent Filter 

Scenario 1: I did not apply any filter 
i want to show this bar chart
k1.png
Scenario 2: I selected 01/Jan/2021 in Month start date
i want to show this bar chart

k2.png

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

k3.png


Raw Data

Month Start DateGeo ContinentUser IdNo Time Used
1-Jan-2021AsiaID15
1-Jan-2021EuropeID110
1-Feb-2021AfricaID12
1-Feb-2021EuropeID13
1-Jan-2021AsiaID22
1-Jan-2021EuropeID22
1-Feb-2021AfricaID23
1-Feb-2021EuropeID23
1-Jan-2021AsiaID315
1-Jan-2021EuropeID315
1-Feb-2021AfricaID315
1-Feb-2021EuropeID315
1-Jan-2021AsiaID41
1-Jan-2021EuropeID41
1-Feb-2021AfricaID41
1-Feb-2021EuropeID41
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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
Untitled.png
If my answer satisfies your requirement, I kindly request you to mark it as Accepted. Thank you

View solution in original post

7 REPLIES 7
v-robertq-msft
Community Support
Community Support

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
Super User
Super User

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
Untitled.png
If my answer satisfies your requirement, I kindly request you to mark it as Accepted. Thank you

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!

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
Community Champion
Community Champion

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:-

image.png

Refer file below:-

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

 

Thanks,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

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

 

vengadesh_p_0-1644398439894.png

 

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.