How to apply filters on these three measures

[JustinDoh1]

I am trying to apply filter such as FacilityCode in {"850", "845"} in all my current three DAX measures, and I am having difficulties.

I do not want to create another duplicate tables just to apply this filter.

I have three measures that have structure like this:

[1] Ave Census Var =
calculate (
                SELECTEDVALUE('vw_census_trend_MonthToDate_2'[Ave Census])
                )
               - COUNT('vw_daily_census_budget'[ID])

----------------------------------------------------------------------------------------

[2] Skill Mix % Budget divide =
Divide(
         [Skill Mix % Budget ID Total]
         ,
          COUNTROWS(vw_daily_census_budget)
)

---------------------

Skill Mix % Budget ID Total =
calculate (
                 COUNTROWS(vw_daily_census_budget),
                 (vw_daily_census_budget[PayerName]) in {"A", "M", "S", "I"}
)

-----------------------------------------------------------------------------------------

[3]

Occupancy Budget % Total =
calculate(
               Divide(
                          [Budget ID Total],
                          calculate(
                                          sum(vw_occupancy[Capacity])
                                         )
                          )
               )

-------------------
Budget ID Total = Count(vw_daily_census_budget[ID]) + 0

All three tables have FacilityCode column.

Three tables:

• vw_census_trend_MonthToDate_2
• vw_daily_census_budget
• vw_occupancy

Thanks for help.

[JustinDoh1]

@v-yiruan-msft Thank you very much! Your feedback was very clear and perfect.

[v-yiruan-msft · Accepted solution]

Hi @JustinDoh1 ,

You can update the formula of those three measures as below:

[1] Ave Census Var

Ave Census Var =
CALCULATE (
    SELECTEDVALUE ( 'vw_census_trend_MonthToDate_2'[Ave Census] ),
    FILTER (
        'vw_census_trend_MonthToDate_2',
        'vw_census_trend_MonthToDate_2'[FacilityCode] IN { "850", "845" }
    )
)
    - CALCULATE (
        COUNT ( 'vw_daily_census_budget'[ID] ),
        FILTER (
            'vw_daily_census_budget',
            'vw_daily_census_budget'[FacilityCode] IN { "850", "845" }
        )
    )

[2] Skill Mix % Budget divide

Skill Mix % Budget ID Total =
CALCULATE (
    COUNTROWS ( vw_daily_census_budget ),
    FILTER (
        vw_daily_census_budget,
        vw_daily_census_budget[PayerName]
            IN { "A", "M", "S", "I" }
            && vw_daily_census_budget[FacilityCode] IN { "850", "845" }
    )
)

Skill Mix % Budget divide =
DIVIDE (
    [Skill Mix % Budget ID Total],
    CALCULATE (
        COUNTROWS ( vw_daily_census_budget ),
        FILTER (
            vw_daily_census_budget,
            vw_daily_census_budget[FacilityCode] IN { "850", "845" }
        )
    )
)

[3] Occupancy Budget % Total

Budget ID Total =
CALCULATE (
    COUNT ( vw_daily_census_budget[ID] ),
    FILTER (
        vw_daily_census_budget,
        vw_daily_census_budget[FacilityCode] IN { "850", "845" }
    )
) + 0

Occupancy Budget % Total =
DIVIDE (
    [Budget ID Total],
    CALCULATE (
        SUM ( vw_occupancy[Capacity] ),
        FILTER ( vw_occupancy, vw_occupancy[FacilityCode] IN { "850", "845" } )
    )
)

If the above one can't help you get the expected result, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards