cancel
Showing results for
Did you mean: 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:

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.

____________
Please join the Power BI User Group if you need help with dashboard design and usability
https://community.powerbi.com/t5/Power-BI-UX-UI-User-Group/gh-p/PowerBIUXUIUserGroup

Subscribe to my medium blog  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.

____________
Please join the Power BI User Group if you need help with dashboard design and usability
https://community.powerbi.com/t5/Power-BI-UX-UI-User-Group/gh-p/PowerBIUXUIUserGroup

Subscribe to my medium blog   