Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.