Custom Clustering

[Hank_]

Hi everybody!

I'd like to cluster durations, and use them in matrix, bar charts, legends, filters.  

These are calculated with DAX. In this way I can only make simple tables (not matrix).

My calculation:

Durations are calculated per each object, that passes different states, as simple subtraction between max_date and min_date.

For example:

ITEM	STATE	DATE
object 1	on draft	date
object 1	start working	date
object 1	finished working	date
object 1	dispatched	date

Duration calculates the total days between last state and first state.

I made automatic clustering, and then can create graphs, but PBI decides the rule and I can't change it. 

So I calculated clusters with this simple formula:

Duration cluster = IF([duration]<30, "OK" , IF([duration]>60, "LATE", "WARNING"))

My question:

How can I produce custom clusters that I can use everywhere? 

Is there a way to change automatic clustering?

[parry2k]

@Hank_ I believe Duration Cluster you added as a measure and you cannot use it the way you want it. Check this Dynamic Segmentation post and make it work in your model.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PhilipTreacy]

Hi @Hank_ 

Your qs How can I produce custom clusters that I can use everywhere? - haven't you already done this with your IF ?

If you have specific clusters/buckets/categrories that you want your data to be assigned to then this is the way I'd approach it.

If you need furhter help please supply some real data that can be worked with.

Regards

Phil

[Hank_]

Hello Philip, here the link to the pbix https://1drv.ms/u/s!ApBVHbcaAbTVhlXycrEG_MfxEdGe?e=ycB4VF 

[Hank_]

Hi Philip, yes I made this clustering with DAX but I cannot use it in legend nor axis in a bar chart: I can put it only in tooltip or values if I change clusters to numbers (1, 2, 3 instead of OK, WARNING, LATE).

I will produce a file: I have to change all company data and have dozens of table in my model.