Changing the Selection within Calculate()

[ThomasDay]

Hello all,

I have prepared a compressed model that can help anyone see what I'm doing and perhaps help me get to an answer.

I pick a group of Comparison facilities using a slicer...and compute their average price.  I pick a "Scorecard" facility and compare it's pricing to the comparisons using the same data table.  All works great provided that the Scorecard facility is also in the Comparison facility group.  I'd like to allow the selected Scorecard Provider to be outside of the Comparison group providers.  (If you download the model and pick Scrd provider 41319 which is a govt owned facility...and not in the comparison group, you'll see that Scrd variables go to blanks...and the matrix blanks out.)

Here is the simple table structure:

Here is what the output looks like...and the formulas:  I cannot for the life of me figure out how to get Scrd Price Per Unit to filter the data file to the Scrd facility unless it's in the initial slicer selection.  Same with Total Scrd Cases.  I've tried a Union of a parallel data table...but got an error that I couldn't get around.  Anyway, any strategies come to mind.  Here's the .pbix file in my dropbox.

[d_gosbell · Accepted solution]

The issue here that that you are generating conflicting filters on the 'data table' table. If you exclude Government from the Comparison Providers, but then select a Scorecard provider that only contains data for Government you end up filtering out all the rows in 'Data Table'.

If the behaviour that you want is for the scorecard measures to ignore the comparison provider selection, the you could do this by wrapping your scorecard measures with another CALCULATE() and use the CROSSFILTER() function to "turn off" the relationship. 

Note: you will need to do this for both [Scrd Price Per Unit] and [Tot Scrd Cases]

eg

Scrd Price Per Unit = CALCULATE(
                           CALCULATE(
                              AVERAGE('DataTable'[Price per Unit]), 
			      FILTER('DataTable', 'DataTable'[ProviderID] = SELECTEDVALUE(ScorecardProvider[ProvdrID]))
                           )
                           ,CROSSFILTER('ComparisonProviders'[ProvdrNo], 'DataTable'[ProviderID], None)
                      )

Tot Scrd Cases = CALCULATE(
                      CALCULATE(
                           SUM('DataTable'[TotalUnits]), 
			   FILTER('DataTable', 'DataTable'[ProviderID] = SELECTEDVALUE(ScorecardProvider[ProvdrID])))
                      ,CROSSFILTER(ComparisonProviders[ProvdrNo], 'DataTable'[ProviderID], None)
                  )

[ThomasDay]

@d_gosbell  Well I'll be darn...I had no idea what to do with crossfilter nor the sequence of nested Calculate actions.   It makes sense that the outer Calculate sets the selection for the inner Calculate but I've not done that before.  Incredibly useful and a welcome solution.  Thank you very much, Tom

[d_gosbell]

---

@ThomasDay wrote:

@d_gosbell  Well I'll be darn...I had no idea what to do with crossfilter nor the sequence of nested Calculate actions.   It makes sense that the outer Calculate sets the selection for the inner Calculate but I've not done that before.  Incredibly useful and a welcome solution.  Thank you very much, Tom

---

Yes, I've used the crossfilter function a few times, but the nested calculates is not actually a technique that I've used much either. But it's handy when you need to change the filter context of an inner calculate() call.