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
CheenuSing
Community Champion
Community Champion

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

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.

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

12 REPLIES 12
ImkeF
Super User
Super User

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 

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

CheenuSing
Community Champion
Community Champion

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!

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

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!

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

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.

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @ImkeF,

 

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

Does it alwayes need to connect with Analysis Services?

 

Regards,

 

Paweł

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?

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

CheenuSing
Community Champion
Community Champion

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!

is there any workaround to avoid refreshing

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

Imke Feldmann (The BIccountant)

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

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

GilbertQ
Super User
Super User

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

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.