cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User I
Super User I

Creating a dynamic summarized table or calculated table

@OwenAuger , @ImkeF , @Seth_C_Bauer, @Sean

 

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Creating a dynamic summarized table or calculated table

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.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

12 REPLIES 12
Super User IV
Super User IV

Re: Creating a dynamic summarized table or calculated table

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
Super User III
Super User III

Re: Creating a dynamic summarized table or calculated table

Hi @CheenuSing,

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 

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User I
Super User I

Re: Creating a dynamic summarized table or calculated table

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Super User III
Super User III

Re: Creating a dynamic summarized table or calculated table

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

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User IV
Super User IV

Re: Creating a dynamic summarized table or calculated table

Hi @CheenuSing

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
Super User I
Super User I

Re: Creating a dynamic summarized table or calculated table

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Super User IV
Super User IV

Re: Creating a dynamic summarized table or calculated table

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Power BI Blog
Super User III
Super User III

Re: Creating a dynamic summarized table or calculated table

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.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Super User I
Super User I

Re: Creating a dynamic summarized table or calculated table

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors