topic Ensuring that my measure also applies the filter in DAX Commands and Tips

Ensuring that my measure also applies the filter

BruceSV — Tue, 29 Aug 2023 06:50:03 GMT

Hi everyone,

I am trying to calculate the utilization of a team of people per period. But I am stuck with an error.

I have two tables. One fact table that holds the time_spent value and a dimensions table that holds the total_capacity of the team member value.

The calculation is fairly straigth forward:

(time_spent / total_capacity) * 100%

So my dax measure is as follows:

Utilization Percentage = CALCULATE( DIVIDE( SUM(tbl_1[time_spent]), SUM(tbl_2[total_capacity])), ALLSELECTED('tbl_1') *100)

However this is giving me an "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

If I remove the "ALLSELECTED" clause the code works but the measure does not take some filters into consideration. Giving me incorrect information.

What I essentially want is a measure that calculates the utilization % but still respects all the applied filters on the visual.

Re: Ensuring that my measure also applies the filter

some_bih — Tue, 29 Aug 2023 09:55:50 GMT

Hi BruceSV ALLSELECTED is most compex function in DAX.

Usually you should have some aggregation in case of this error.

My question to you why in your ALLSELECTED you put table_1 as in CALCULCATE there are also table_2 and why it is *100) at end? Did you try pure ALLSELECTED ()?

Re: Ensuring that my measure also applies the filter

ERD — Tue, 29 Aug 2023 10:57:03 GMT

BruceSV , I think you've put *100 in the wrong place..

Utilization Percentage = CALCULATE ( DIVIDE ( SUM ( tbl_1[time_spent] ), SUM ( tbl_2[total_capacity] ) ) * 100, ALLSELECTED ( 'tbl_1' ) )

Re: Ensuring that my measure also applies the filter

BruceSV — Tue, 29 Aug 2023 11:21:05 GMT

Hi some_bih - I have tried this but that doesn't work unfortunately.

Re: Ensuring that my measure also applies the filter

BruceSV — Tue, 29 Aug 2023 11:28:41 GMT

Although the syntax is approved the results are not what I expected. The measure now doesnt apply the legend anymore.

Edit - It actually did work and surfaces a different problem, need to update my data model. Thanks ERD