Using SELECTEDVALUE with Calculated Table workaround?

[BradAF]

Hello,

I have a report that currently shows how many items were added to a database during a scan and the difference compared to the previous run. Clicking on any date in the graph changes the total findings to that date, and updates the change. If no date is selected, it defaults to the latest:

It works great, but what I would also like to do is create two other table visualizations that actually show the items that were ADDED and REMOVED between the selected scan and the previous scan, so we can see what items actually changed instead of just a count. I created this query that works ONLY for the most recent scan and the previous one to show items that were added; i.e., the data in the visualization never changes when selecting a new date:

Added = 
var __selectedDate =
    SELECTEDVALUE(
        calculated_filtered[scanned],
        MAX(calculated_filtered[scanned])
    )

var __previousDate =
    CALCULATE (
        MAX(calculated_filtered[scanned]),
        calculated_filtered[scanned] < __selectedDate
    )

var __newerTable =
    CALCULATETABLE(
        calculated_filtered,
        calculated_filtered[scanned] = __selectedDate
    )

var __olderTable =
    CALCULATETABLE(
        calculated_filtered,
        calculated_filtered[scanned] = __previousDate
    )

return

EXCEPT(
    SELECTCOLUMNS(
        __newerTable,
            "provider",[provider],
            "recommendation",[recommendation],
            "resourcegroup",[resourcegroup],
            "resourceid",[resourceid],
            "resourcename",[resourcename],
            "subscription",[subscription],
            "taskid",[taskid]
       ),
    SELECTCOLUMNS(
        __olderTable,
            "provider",[provider],
            "recommendation",[recommendation],
            "resourcegroup",[resourcegroup],
            "resourceid",[resourceid],
            "resourcename",[resourcename],
            "subscription",[subscription],
            "taskid",[taskid]
    )
)

I know this may not work since I'm using a Calculated Table and it may only run once, but even after selecting a date and hitting 'Refresh' it doesn't update the list so I'm not sure if I'm just doing something wrong or if there's a workaround to making something like this work. Any thoughts or help is appreciated!

[AllisonKennedy · Accepted solution]

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.

[AllisonKennedy · Accepted solution]

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]

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?

[AllisonKennedy · Accepted solution]

@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/?...
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-powerapp

[BradAF]

Thanks, 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!

[AllisonKennedy]

You're welcome. Enjoy Power Query parameters - they're fun, and feel free to shout out if you get stuck or confused.