topic How to apply filters on these three measures in DAX Commands and Tips

How to apply filters on these three measures

JustinDoh1 — Thu, 29 Dec 2022 00:15:38 GMT

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.

Re: How to apply filters on these three measures

Anonymous — Thu, 29 Dec 2022 04:20:22 GMT

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

Re: How to apply filters on these three measures

JustinDoh1 — Thu, 29 Dec 2022 23:51:01 GMT

Anonymous Thank you very much! Your feedback was very clear and perfect.