Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Flawn
Helper I
Helper I

Trying to recreate a DAX function for Power Query so I can create a table to Unpivot

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:

Flawn_1-1637809483962.png

 

Flawn_0-1637809415500.png

 

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.

Flawn_2-1637809606672.png


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_clientmcs_sessionmcs_sessiondatedisability CALD ATSI 
10D9667625/07/2018 1:30111
1E081275915/10/2018 2:00111
1F16CFA5222/10/2018 1:45111
2982B788F8/01/2019 5:001  
25C6F3A9A4/02/2019 3:301  
30FBED8CB10/07/2019 23:30 1 
4EF2969486/07/2019 0:00 11
44428409911/07/2019 2:30 11
4A8DA3C3911/07/2019 6:00 11
42DB7363B15/07/2019 23:30 11


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.

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@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?

 

 


Please @mention me in your reply if you want a response.

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

View solution in original post

3 REPLIES 3
AllisonKennedy
Super User
Super User

@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?

 

 


Please @mention me in your reply if you want a response.

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

@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/

 


Please @mention me in your reply if you want a response.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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