topic Re: Creating a dynamic summarized table or calculated table in Desktop

Creating a dynamic summarized table or calculated table

Anonymous — Mon, 19 Jun 2017 03:05:09 GMT

My understanding of the summarise function is to create a table based on certain groupings of source data. Therefore this becomes static. If I use filters and want to change the summary table it does not work.

My questions are

1) Is this achievable using DAX.

2) Is this acheivable using M Language ( power query) in which case how to pass the value of the selections in slicer to the query.

Appreciate all of your help.

Cheers

CheenuSing

Re: Creating a dynamic summarized table or calculated table

GilbertQ — Mon, 19 Jun 2017 04:03:27 GMT

I think you might be able to do it with DAX.

I would think that as soon as you try and pass values back to the Query Editor or M Language you would then have to refresh the data in order to see the changes.

Re: Creating a dynamic summarized table or calculated table

ImkeF — Mon, 19 Jun 2017 04:32:23 GMT

Hi Anonymous,

I have no idea if this is possible in DAX, as a slicer-selection returns a text which you would then have to pass "as a variable" to a DAX-expression.

This is possible in M (see the general concept here: http://www.thebiccountant.com/2017/03/22/use-slicers-for-query-parameters-in-powerbi/ ) but as GilbertQ already mentioned, that would mean that you have to hit the refresh manually after the slicer-selection has been changed. If that's OK for you & you need help in implementing it for your usecase, just let me know.

Cheers, Imke

Re: Creating a dynamic summarized table or calculated table

Anonymous — Mon, 19 Jun 2017 06:46:42 GMT

Hi ImkeF

Please show me any example pbix to acheive this.

Hi GilbertQ

If this is doable in DAX how to go about it.

Cheers

CheenuSing

Re: Creating a dynamic summarized table or calculated table

ImkeF — Mon, 19 Jun 2017 08:02:52 GMT

Will do tomorrow if no DAX-solution shows up until then :-)

Re: Creating a dynamic summarized table or calculated table

GilbertQ — Mon, 19 Jun 2017 21:37:13 GMT

Hi Anonymous

We would need a working example to understand what you are trying to achieve.

I would imagine that the data would be loaded into a summarized table using the new table functionality, and then I would use DAX to create dynamic measures based off that table. I have done something similar in the past for a bit of a complex maths calculation. Unfortunately I cannot share that information. But it was so that I could have a line going across the data, and if they selected more or less years, the line would then change to reflect this.

Re: Creating a dynamic summarized table or calculated table

Anonymous — Tue, 20 Jun 2017 03:32:28 GMT

Hi GilbertQ

I want to build the reduced dynamic data set using summarise function . I know about creating measures dynamically. I am interested in the reduced dynamic data set based on filters selected.

Cheers

CheenuSing

Re: Creating a dynamic summarized table or calculated table

GilbertQ — Tue, 20 Jun 2017 03:45:16 GMT

Ok thanks I think that you will have to see what Imke can come up with her amazing skills. I can only think that once you have loaded the data you cannot filter it in terms of reducing the size without having to reload the data.

Re: Creating a dynamic summarized table or calculated table

ImkeF — Tue, 20 Jun 2017 08:08:36 GMT

Here is the link to the file: https://1drv.ms/u/s!Av_aAl3fXRbehap3IIpK9lEWhPkdwA

When you change the slicer-selections, the Table1 will change it's columns accordingly - after you've hit refresh!

When you apply it to your model, you might find that it doesn't work at the first go. Don't know why that is, but the solution is to close the file and re-open it. At least, this worked for me :-)

Edit: Changed link to the file: The measure that harvests the slicer-selection must be used in the report somewhere.

Re: Creating a dynamic summarized table or calculated table

Anonymous — Tue, 20 Jun 2017 08:47:08 GMT

Hi ImkeF

Thanks. The only caveat is we will have to refresh for every change in the selection.

I will try it. Your source data can that be given in excel for me to play around.

Cheers

CheenuSing

Re: Creating a dynamic summarized table or calculated table

paweldm — Mon, 12 Mar 2018 14:47:55 GMT

Hi ImkeF,

Can you explain step by step how your solution should work ?

Does it alwayes need to connect with Analysis Services?

Regards,

Paweł

Re: Creating a dynamic summarized table or calculated table

ImkeF — Mon, 12 Mar 2018 15:02:41 GMT

Hi paweldm,

yes, this solution only works via the connection to the Analysis server instance. So it will NOT work in the service in PowerBI.com.

Currently I cannot see which steps are missing in the documenation (mine and Rob Collie's blogpost). What exactly are you missing?

Re: Creating a dynamic summarized table or calculated table

dpombal — Wed, 11 Apr 2018 15:31:31 GMT

is there any workaround to avoid refreshing