Adding an IF statement to existing Measure to Filter only on 1 Row of results

[adoster]

Hello,

Apologies that I don't have good sample data for this question. If this doesn't make sense I'll try to simplify.

I would like to add an IF statement to filter on an existing Measure so that only 1 row in my visual is filtered

Example: I need to add an IF FILTER only on the Vascular Surgery Row

	2019	2020	2021
VAD	20	60	100
Heart	30	70	101
Valve	40	80	102
Vascular Surgery	50	90	103

Here is the Existing Measure: 

CountByRank =

     VAR res =
          CALCULATE (
               COUNT ( 'All Data'[Procedure_Desc] ),
               FILTER ( 'All Data', 'All Data'[LowestRank] = 1 ),
               ALLEXCEPT ( 'All Data', 'All Data'[Service Date] )
)
RETURN
IF ( ISBLANK ( res ), 0, res )

Here is the IF statement I need to add to the above measure.

IF ('All Data', 'All Data'[Provider Groups] = "Vascular Surgery")
     FILTER('ProcedureList', 'ProcedureList'[Service] = "Vascular Surgery")

Not sure how/where this new statement should fit in with the original.

Thanks for any assistance!

[adoster · Accepted solution]

I found a solution to this today

1. Created a Metric to Calculate everything exept Vascular Surgery

2. Created a Metric to Calculate Vascular Surgery Filtering only by the Provider group

3. Created a Metric to Total 1 & 2

[Russell-PBI]

Hi @adoster, it depends on what your model looks like as I can see you have at least two tables (All Data and ProcedureList).

It might be possible to solve it with a calculated column. Your IF statement isn't valid, but it looks like you want to exclude all other values except the "Vascular Surgery" service for the "Vascular Surgery" provider group only? If so, you may be able to use a calculated column like this:

Include in Count Flag =
IF(
     'All Data'[Provider Groups] = "Vascular Surgery"
           && RELATED('ProcedureList'[Service]) = "Vascular Surgery"
      || NOT 'All Data'[Provider Groups] = "Vascular Surgery"
,     1
,     0
)

You could then use this as a filter on your visual, page or report; or reference it in your existing measure:

CountByRank =
     VAR res =
          CALCULATE (
               COUNT ( 'All Data'[Procedure_Desc] ),
               FILTER ( 'All Data', 'All Data'[LowestRank] = 1 ),
               ALLEXCEPT ( 'All Data', 'All Data'[Service Date] ),
               'All Data'[Include in Count Flag] = 1
)
RETURN
IF ( ISBLANK ( res ), 0, res )

This may only work if your ProcedureList table is the 'one' side of a one:many relationship.