Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Everyone, and thank you in advance for your help.
So I have created a bucketing table so that I can attempt to generate a particualar stacked column chart. These are posted below:
Mechanically, this works, but doesn't fulfill the desired outcome. Through a series of measures we count the Disability, CALD, etc. (usertype) data - but due to the fact that the only way we can figure out to generate this table is with the #of sessions buckets as values in a column - the graph that results always looks something like this.
The users *really* want the # of sessions column as the stack value, and the usertype as the X value. As far as I can tell the only way to do this is with the unpivot function, but unfortunately unpivoting isn't as simple as it usually is here.
The session counts we are generating are reactive by a date slider, so we have generated the mith measures. But, if we want to unpiviot the bucket table we need to generate columns in Power Query - at least, as far as we can tell this is the only solution. Other alternatives, if simpler, would be appreciated.
We are working with a base table that looks something like this:
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 |
And are using the following expressions to generate the measures we need to count the disability/cald/atsi values by the number of sessions each client has:
First, to count the sessions for each client:
# of Sessions in Client (Measure All) =
CALCULATE(CALCULATE(COUNT(ClientInSessionByCase[mcs_client])),
ALLEXCEPT(ClientInSessionByCase, ClientInSessionByCase[mcs_client]))
Then, to bucket the values:
Session Bucket (All) =
COUNTx(filter(VALUES(ClientInSessionByCase[mcs_client]),
[# of Sessions in Client (Measure All)] >=Min('Session#Buckets'[Start Limit]) &&
[# of Sessions in Client (Measure All)] <=max('Session#Buckets'[End Limit])),
ClientInSessionByCase[mcs_client])
Then, we count for each bucket in the bucket table for the particular usertypes:
Session Bucket (ATSI) =
CALCULATE(
[Session Bucket (All)],
'ClientInSessionByCase'[ATSI Y/N] IN { "1" }
)
Now, I know this is daunting - but we need to somehow either recreate this proccess for power query - or perform a similar proccess as to generate the same values for the bucket table. This will allow us to then unpivot that table, which will allow us to then, hopefully, switch which values end up in the stack of the graph, and which end up as the X values.
Thank you again in advance for the help - and, as stated before: happy to take any other paths that will lead us to the primary objective: switching the x and stack values for the stacked column chart.
Solved! Go to Solution.
@Flawn I'm not sure if I have read your post carefully enough to get all your requirements and terminology right, but is the attached (below signature) similar to what you're looking for?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@Flawn I'm not sure if I have read your post carefully enough to get all your requirements and terminology right, but is the attached (below signature) similar to what you're looking for?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Yes @AllisonKennedy , this is the result I am looking for.
It looks like you unpivoted the initial data before moving it into the bucket table.
My primary concern with that - for the real data - is that there is quite a lot of it. I was hoping that by unpivoting after its been bucketed it would reduce the amount of data being used.
But if this is the best solution - and it is a good one - we can just try to make it work.
Can i ask what the DimClients table is for?
Regards,
Flawn
Unpivoting the data first is generally what Power BI prefers - lots of rows, fewer columns.
https://www.myonlinetraininghub.com/excel-tabular-data-format - this post is for Excel, but Power BI generally follows similar rules in that you should strive for database format for your queries.
The DimClients table is a start at getting the optimal Star Schema for Power BI reports - you shouldn't have one big flat table, but rather should split out key dimensions:
https://excelwithallison.blogspot.com/search?q=it%27s+complicated
https://www.sqlbi.com/articles/power-bi-star-schema-or-single-table/
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |