Refresh calculated tables dynamically based on slicer selection

[Anonymous]

@Greg_Deckler ,

We have a specific requirement of showing tabular data for two categories - Employee tested data and Not Tested data. These categories are shown on slicer. Initially thought of achieving this by using calculated tables that can be refreshed at run time or by using except function along with some other logic.

After lot of research, couldn't achieve it. Is there any provision to make it happen like in parametrized SSRS reports that can fetch data from database at run time ? Please suggest your inputs. Thanks

[Anonymous]

Hi @Anonymous ,

I'm afraid it's not available to create a calculated table dynamically based on slicer. Once the table was created, it won't be changed by the slicer value.

Best Regards,

Jay

[Anonymous]

Hi @Anonymous ,

Correct, we cannot change the data of calculated tables dynamically based on slicer value. Thanks for your suggestion.

[Anonymous]

@mahoneypat ,

Thanks for your inputs.

I have tried the second one bringing entire data and then adding a column in slicer but this won't work because Not tested set of data needs to be calculated at run time. Suppose user applied few filters and based on that viewing data for tested employees then those employees needs to be excluded from the master set to show Not tested scenario.

Hope this explanation helps. I will definitely try the first one. Meanwhile if you could suggest more inputs.

[mahoneypat]

If your not tested data is just a subset of your data, you should have a single table and use measures to show/hide tested/not tested.  Please share some example/mock data and what your desired output is.  Even better to send a link to a pbix file.

Regards,

Pat

[Anonymous · Accepted solution]

Hi @mahoneypat ,

Tested data is not just a subset of data. It's not fixed. We need to change data of calculated tables dynamically based on slicer value / selections at run time. Tested / Not Tested data tables needs to be refreshed at run time. For now I have improvised it and created different physical tables for Tested and Not Tested both scenarios using Cross join as part of SQL script.

In Power BI then we can filter this dataset and toggle between them using cardinality (Many to Many) and this has been configured based on Slicer field. Thanks for your suggestion.

Let me know in case of any concerns.

[mahoneypat]

Have you considered using query parameters?  You can make a parameter and use that value in your query.  If you save the file as a pbit (template file), the user will get prompted for which type of data they'd like.

https://docs.microsoft.com/en-us/power-query/power-query-query-parameters

Also, why not always bring in both sets of data appended into a single table, adding a column you can use as a slicer on your report (tested or not tested).

Regards,

Pat