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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.