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
Anonymous
Not applicable

Distribution of duration and difficult subdivision

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 : 

enzomanfredi_0-1653502012274.png

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 : 

enzomanfredi_1-1653502384613.png

And here is how it looks like : 

enzomanfredi_2-1653502412738.png


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

enzomanfredi_3-1653503144112.png

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

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

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.

View solution in original post

2 REPLIES 2
sturlaws
Resident Rockstar
Resident Rockstar

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
Not applicable

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  : 

enzomanfredi_0-1653644847523.png

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 ! 

enzomanfredi_1-1653645414115.png


Now I have to figure out how to change colors 😛 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.