cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ThomasDay Established Member
Established Member

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

Accepted Solutions
Super User
Super User

Re: Changing the Selection within Calculate()

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
Super User
Super User

Re: Changing the Selection within Calculate()

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

ThomasDay Established Member
Established Member

Re: Changing the Selection within Calculate()

@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

Super User
Super User

Re: Changing the Selection within Calculate()


@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
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,323)