Distribution of duration and difficult subdivision

[Anonymous]

Hello there ! 
Hope you're doing well 🙂 
I'm reaching you for a problem I'm facing since quite a moment now. 
I want to show the distribution of a lot of duration in minutes (few thousands of them). To show this, here is a graph that represents my data quite precisely but that is unusable in that state : 

There is a BIG group in the fisrt hundreds and then it's super-spread.
There is a special threshold (fixed by my client) for durations that go beyond 30 minutes. I need to show it. Here we cannot even where it is. 

So, I'm a grown up now so I tried to subdivide my data by creating groups but it's still too big and unhandy. 

One trick that I've found was to create few measures that count how many iterations I have for durations between : 
- 0 to 30 min
- 30 to 60 min
...
- 150 to 180 min 
- and above 180 min
I know I'm comparing groups that are uneven (especially one that is infinite) but that's the only way I found to actually represent handly my data...
Here is how it's ploted in my graph : 

And here is how it looks like : 

But the columns take only 25% of the total X-axis' range, no matter how many measures I put... 

And that is not how I want my data to be represented. 😞

There would be two ways of helping me, in my opinion : 
- Either telling me how to do without these homemade groups in order to have the same results but on the whole range of the X-axis;

- Either keeping these groups but having the possibility of having the bars taking the whole range on the X-axis. It has to be presented, and that way they are too small...

Or you can also tell me that I'm trying to fit one cube into a circle and that I did not understand anything at Power BI 😛

Please have a wonderful day, see you soon ! 
Enzo Manfredi

[sturlaws · Accepted solution]

Hi, @Anonymous,

one possible strategy is to create a table, call it e.g. ManualTable. using the "Enter data"-functionality in Power BI. Make it 2 columns wide, MeasureName and SortNum. On the rows of MeasureName, enter the name of the measures you have created. In the second column, SortNum, enter the row number, so that you are able to sort the colunm MeasureName by this. This table should not have relationships to any other table.

Next create this measure:

newMeasure = 
_measure=calculate(selectedvalue(ManualTable[MeasureName])
return
switch(true(),
_measure="your first measure",[your first measure],
_measure="your second measure",[your second measure],
_measure="your third measure",[your third measure]
)

Now add ManualTable[MeasureName] to the x-axis, and newMeasure in the value-field.

It is possible to achieve something similar to this using the Power BI Preview feature Field Parameters (https://www.sqlbi.com/articles/fields-parameters-in-power-bi/)

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Hello Sturla ! 
Thanks for your quick response ! 🙂 

I mixed both of the methods mentionned.

As I understood the Field Parameters feature was doing the same task than creating a new table as described earlier. This is my result in my case by using the Field Parameter feature  : 

Nothing wild, it's simply what you described to do. In my case your "MeasureName" is my "Mesure_C1_INT".

Then I created my "newMeasure" using your formula that I changed a bit (it was showing errors so I thought it was missing some stuff) : 

newMeasure = 
var _measure=calculate(selectedvalue(Mesure_C1_INT[Mesure_C1_INT]))
return
switch(true(),
_measure="_groupe_C1_00-30_Intervention",[_groupe_C1_00-30_Intervention],
_measure="_groupe_C1_30-60_Intervention",[_groupe_C1_30-60_Intervention],
_measure="_groupe_C1_60-90_Intervention",[_groupe_C1_60-90_Intervention],
_measure="_groupe_C1_90-120_Intervention",[_groupe_C1_90-120_Intervention],
_measure="_groupe_C1_120-150_Intervention",[_groupe_C1_120-150_Intervention],
_measure="_groupe_C1_150-180_Intervention",[_groupe_C1_150-180_Intervention],
_measure="_groupe_C1_180-inf_Intervention",[_groupe_C1_180-inf_Intervention]
)

 
Then I ploted "newMeasure" in the Y-axis and "Mesure_C_INT" in the X-axis. At first it didn't work but by adding a segment that filters my graph, it's working wonderfully as you can see ! 

Now I have to figure out how to change colors 😛 

Thank you so much Sturla. 
Have a good day.
Enzo