cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasDay
Impactful Individual
Impactful Individual

Changing the Selection within Calculate()

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:

Structure.jpg

 



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.PricingQuestion.jpg

 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

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)
)

 

 

View solution in original post

3 REPLIES 3
d_gosbell
Super User
Super User

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
Impactful Individual
Impactful Individual

@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


@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. 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.