Count Bins that adjust w/ Date Slicer for a Stacked Column Chart

[Flawn]

Hello Everyone,

I've inherited a very messy dataset that i'm working with in PBI. I'd consider myself still fairly amatuerish when it comes to the program, and I've unable to fulfill a certain design request that was made.

I was provided with a sample image to get an idea of the desired outcome. They also want to remove the limitation that the data display is 'set' to fortnightly.

I was able to quite easily arrange the data to categorize clients into the four groups, and the date slicer was also easy: but i've run into some issues with breaking up the # of sessions for each client into bins. While I can create the bins easily enough using the grouping system, the desired outcome would be for the number of entries in each bin to adjust based on the date slicer.  So, as an example, a DFV client that was in the 4-10 sessions bin if the slicer is set to a month, could drop into the 2-3 sessions bin if the slicer is set to the month. At the moment all sessions the client has ever had are counted, regardless of how the slicer has been set. This of course means that everyone is being put into innappropriately high buckets.

The Second issue I am having is a matter of data Display, a shot of the draft chart is provided below:

Simply put, I would like to 'flip' the axis and values. So that the x-axis is dedicated to each category, while the stacks track each bin. The way the data has been prepared, the chart in question doesn't seem willing to make this happen - some guidance as to the appropriate arrangement within the axis/legend/values to make this work would be appreciated. 

While I can't share the file in question - the stuff is very private - I've recreated the format of the relevent data in the table below:

mcs_client	mcs_session	mcs_sessiondate	disability	CALD	ATSI
1	0D966762	5/07/2018 1:30	1	1	1
1	E0812759	15/10/2018 2:00	1	1	1
1	F16CFA52	22/10/2018 1:45	1	1	1
2	982B788F	8/01/2019 5:00	1
2	5C6F3A9A	4/02/2019 3:30	1
3	0FBED8CB	10/07/2019 23:30		1
4	EF296948	6/07/2019 0:00		1	1
4	44284099	11/07/2019 2:30		1	1
4	A8DA3C39	11/07/2019 6:00		1	1
4	2DB7363B	15/07/2019 23:30		1	1

 
At the moment I'm the way I am generating the number a count of the sessions each client has is through a pretty simple calculated column. I recognize the solution here is probably in a measure: but i am not as confident with those:

# of Sessions in Client = 
CALCULATE(CALCULATE(COUNT(ClientInSessionByCase[mcs_client])), ALLEXCEPT(ClientInSessionByCase, ClientInSessionByCase[mcs_client]))

Thank you all in advance for any assistance you provide - and your patience with this clumsy, amatuer, PBI user.

[v-shex-msft]

HI @Flawn,

According to your description, It seems like you want to show your visual graph with dynamic ranges based on aggregate values.
I'd like to suggest you create a table with all ranges, then you can write a measure expression with switch function to calculate based on the current category.

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup...

Static segmentation – DAX Patterns

Regards,

Xiaoxin Sheng

[amitchandak]

@Flawn , based on what I got so far, You need to bucket the measure. for that you need an independent table.

refer if these blog can help

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[Flawn]

Thanks for the response,

While it's an okay start, I am afraid the solutions you provide do not work well for my particular case.

Following the steps in your video we produced this table:

A good start, but unfortunately this leaves us stuck with the same problem we started with:

The # of Sessions is still used for the X Axis, while the categories - ATSI, CALD, etc - are used for the stacks. We need to swap this around.

We tried to fix this ourselves, but the way in which you created your buckets does not translate well to the way our database is arranged. As you can see in the sample we provided above, each of the categories is its own column, and one client can be in multiple columns. This meant that the switch function wouldn't work to generate our bins, nor was the min/max system that you used useful for filtering them.

Thank you in advance for further guidance.

[v-shex-msft]

Hi @Flawn,

Perhaps you can try or unpivot these value fields to convert them to attribute and value. Then you can use attribute on legend fields and use measure on value field with switch function and if statement to check both axis and legend values at the same time and redirect them to specific calculations.

Unpivot columns (Power Query) (microsoft.com)
Regards,

Xiaoxin Sheng

[Flawn]

Hi @v-shex-msft 

I am afraid this doesn't seem it will be a viable solution. The value field are generated either through measures or calculated columns, and as far as I can tell one cannot use the unpivot tools with these. The source columns are far more complex and often based in numerical id-keys that would not unpivot in a way that could be easily parsed.

-Flawn

[v-shex-msft]

HI @Flawn,

Yes, these suggestions did not suitable for complex datasets and structures. Is it possible to move these parts to be processed on your datasource side, then you can only need to use power bi to get data and design reports features? 

BTW, I also hope others can share some more suggestions for your situation.

Regards,

Xiaoxin Sheng

[Flawn]

Hi @v-shex-msft,

Unfortunately, the datasource is tightly controlled and its structure can't be meaningfully altered. I'll keep trying to find solutions and welcome further advice from anyone that has some to give!

-Flawn