cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

DAX Help for Sum a measure filter by another measure

Hi,

I have struggled to create DAX for measure Num of Class Days with no blank usage.

First I have created this measure:

``````Num of Class Days =
CALCULATE (
COUNT ( UsageRecord[Date] ),
FILTER ( UsageRecord, UsageRecord[Holiday]  IN { FALSE } )
)``````

Then I have created this measure too:

``````Num of Usage =
CALCULATE (
COUNT ( UsageRecord[ClassRoomID] ),
FILTER ( UsageRecord, UsageRecord[Took Attendance] IN { TRUE }
&& UsageRecord[Holiday] IN { FALSE } )
)``````

After that, I want to create a DAX for measure sum of Num of Class Days filter by num of usage not blank or >0. I have created this DAX but not working:

``````Num of Class Days with no blank usage =
SUMX ( UsageRecord, [Num of Class Days] && [Num of Usage] > 0 )``````

I am very happy to say thanks for your help.

I also have attached the sample data on this link below:

Thank you for your help.

Best,
L

1 ACCEPTED SOLUTION
Super User

The SUMX function you're using iterates over each row of the 'UsageRecord' table and evaluates the expression provided. In your case, you're trying to evaluate '[Num of Class Days] && [Num of Usage] > 0', which isn't quite right.

Instead, you should be using an IF statement inside the SUMX to check the condition of 'Num of Usage' for each row and then summing 'Num of Class Days' accordingly.

Here's a revised DAX for your measure:

Num of Class Days with no blank usage =
SUMX(
UsageRecord,
IF([Num of Usage] > 0, [Num of Class Days], 0)
)
This DAX formula will iterate over each row in the 'UsageRecord' table. For each row, it checks if 'Num of Usage' is greater than 0. If it is, it takes the value of 'Num of Class Days' for that row; otherwise, it takes 0. Then, it sums up all these values to give you the total 'Num of Class Days' where 'Num of Usage' is not blank or greater than 0.

Super User

The SUMX function you're using iterates over each row of the 'UsageRecord' table and evaluates the expression provided. In your case, you're trying to evaluate '[Num of Class Days] && [Num of Usage] > 0', which isn't quite right.

Instead, you should be using an IF statement inside the SUMX to check the condition of 'Num of Usage' for each row and then summing 'Num of Class Days' accordingly.

Here's a revised DAX for your measure:

Num of Class Days with no blank usage =
SUMX(
UsageRecord,
IF([Num of Usage] > 0, [Num of Class Days], 0)
)
This DAX formula will iterate over each row in the 'UsageRecord' table. For each row, it checks if 'Num of Usage' is greater than 0. If it is, it takes the value of 'Num of Class Days' for that row; otherwise, it takes 0. Then, it sums up all these values to give you the total 'Num of Class Days' where 'Num of Usage' is not blank or greater than 0.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.