Forum Discussion
Using SELECTEDVALUE with Calculated Table workaround?
- You've created that DAX as a table correct?
Only Measures will recalculate based on slicer selection, so you will need to use that table inside a MEASURE dax formula, then it will respond to your slicer selection without need a refresh.
Challenge is just getting the items down to one 'scalar' value, so you may find CONCATENATEX to be helpful here. - BradAF
Glad the workaround is helping a bit.
Unfortunately, REFRESH only refreshes the data model data, not the slicer inputs. Power BI order of operations is basically as follows:
Raw data source updates/changes
Power Query operations (M code)
DAX tables and columns
Slicers
DAX measures
When you click Refresh, it will go back to the Raw data source and check for updates, apply the query changes from Power Query, then calculate that DAX tables and columns and THEN apply the slicer selections.
You may be able to use the PowerApps visual to ask the user for an input and then need to use that input inside the Power Query filter (using M NOT DAX). Then when you click refresh it will take that selection into account. This would be using Parameters inside Power Query, in combination with the PowerApps add in.
https://radacad.com/power-query-parameters-vs-what-if-parameters-power-bi-implementation-use-cases/?ref=818
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-powerapp
Only Measures will recalculate based on slicer selection, so you will need to use that table inside a MEASURE dax formula, then it will respond to your slicer selection without need a refresh.
Challenge is just getting the items down to one 'scalar' value, so you may find CONCATENATEX to be helpful here.
Sorry for the delay, but yes - that DAX creates a new table, and the visualizer was using columns from that (calculated?) table.
I tried the CONCATANEX you suggested, with UNICHAR to do the newline as a delimiter and it works pretty well! Surprisingly, turning Word Wrap off removes the newline row separator but it works for 'at a glance' and updates as I click on different dates of the graph visualizer as expected
However, I would still like to be able to refresh the original DAX created tables 'Added' and 'Removed', even if I had to click Refresh after selecting a date in the graph visualizer or another slicer.
I tried creating a new page, adding a table visualization pulling columns from the DAX created tables ('Added' and 'Removed') and then adding a slicer on this page with a field to the 'calculated_filtered[scanned]' column from which the 'Added' and 'Removed' tables are calculated from, and it doesn't change:
Any thoughts on how I can get the DAX created tables to update after a refresh, using a slicer which references a column used in the DAX calculation?
- BradAF
Glad the workaround is helping a bit.
Unfortunately, REFRESH only refreshes the data model data, not the slicer inputs. Power BI order of operations is basically as follows:
Raw data source updates/changes
Power Query operations (M code)
DAX tables and columns
Slicers
DAX measures
When you click Refresh, it will go back to the Raw data source and check for updates, apply the query changes from Power Query, then calculate that DAX tables and columns and THEN apply the slicer selections.
You may be able to use the PowerApps visual to ask the user for an input and then need to use that input inside the Power Query filter (using M NOT DAX). Then when you click refresh it will take that selection into account. This would be using Parameters inside Power Query, in combination with the PowerApps add in.
https://radacad.com/power-query-parameters-vs-what-if-parameters-power-bi-implementation-use-cases/?ref=818
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-powerappThanks, Allison, for the information on the order of operations - that helps a lot with trying to understand what's going on!
I tried poking around briefly at the parameters and I think the PowerQuery parameters may be my best bet to get a dynamically generated list of dates to use, so I will keep poking at that.
Thanks again!
You're welcome. Enjoy Power Query parameters - they're fun, and feel free to shout out if you get stuck or confused.