Attempting to create filter off of measure value

[Thomas_MedOne]

I'm finding a lot of things out there on this topic but nothing specifically for what I'm looking for.

I have two tables (three actually).

1. Admissions table

2. Provider Visits table

3. Provider Facilities table.

I have relationships between these tables all of them are many to many because of the way the data is going. I have a visual for the count of visit a provider had on a given day.

I'm using facility name from Table 3 and the count from table 2.  But the relationship is apparently using the one routed through admissions to get the values.

What is happening is there are two providers going to this facility. When I choose the provider in the slider, it give me ALL visits calculated for both providers not just the provider whose building it is.  

So, my solution would be that I created a measure:

CurrentProvider = SELECTEDVALUE(ProviderFacilities[ID])

And my card shows the ID.

Now, I want to put a fitler on the bar graph that says if the VisitsPerProvider[ProviderID] equals the [CurrentProvider] then we're good.  I tried a measure and that didn't work. I tried a column in the VisitsPerProvider Table:

isPartOfFilter = If (VisitsPerProvider[ProviderID] = [CurrentProvider],1,0)

but every value is 0 because it doesn't recognize the measure.

I don't know how to get this value to match so I can filter on it.  What are the ideas?

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

[Thomas_MedOne]

Are you suggesting I do this?

isPartOfFilter = If (VisitsPerProvider[ProviderID] = SELECTEDVALUE(ProviderFacilities[ID]),1,0)

[Thomas_MedOne]

I just tried this and it didn't work.

[tayloramy · Accepted solution]

Hi @Thomas_MedOne,

You’re running into two classic issues at once: (1) ambiguous filter paths from many-to-many relationships, and (2) trying to use a measure inside a calculated column (columns are computed at refresh time and can’t “see” slicer/visual context). The fix is to drive the visual with a measure that explicitly applies the selected ProviderID to your Visits table.

 

1. Keep your existing measure that captures the selected provider:

   CurrentProvider = SELECTEDVALUE(ProviderFacilities[ID])

   Ref: SELECTEDVALUE
2. Create a Visits measure (if you don’t already have one):

   Visits = COUNTROWS(VisitsPerProvider)

3. Create a “filtered” version of that Visits measure that applies the selected provider via TREATAS:

   Visits (Selected Provider) =
   VAR prov = [CurrentProvider]
   RETURN
   CALCULATE(
       [Visits],
       TREATAS( { prov }, VisitsPerProvider[ProviderID] )  -- force the provider filter here
   )

   Ref: TREATAS
4. Use Visits (Selected Provider) on your bar chart instead of the unfiltered Visits measure.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[Thomas_MedOne]

I solved it by creating a measure:

PPVisits = CALCULATE(Count(VisitsPerProvider[VID]),VisitsPerProvider[ProviderID] = SELECTEDVALUE(ProviderFacilities[ID]))