cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CheenuSing Super Contributor
Super Contributor

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
Super User

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.

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

Proud to be a Datanaut!

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
Super User

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 Datanaut!"
Power BI Blog
Super User
Super User

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 

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

Proud to be a Datanaut!

Imke Feldmann

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




CheenuSing Super Contributor
Super Contributor

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
Super User

Re: Creating a dynamic summarized table or calculated table

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

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

Proud to be a Datanaut!

Imke Feldmann

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




Super User
Super User

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 Datanaut!"
Power BI Blog
CheenuSing Super Contributor
Super Contributor

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
Super User

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 Datanaut!"
Power BI Blog
Super User
Super User

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.

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

Proud to be a Datanaut!

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

CheenuSing Super Contributor
Super Contributor

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 383 members 3,229 guests
Please welcome our newest community members: