DAX Help for Sum a measure filter by another measure

[Anonymous]

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 )

Please help me with this.

I am very happy to say thanks for your help.

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

https://www.dropbox.com/s/jj5gzkzsu96oryx/Sample%20Data.pbix?dl=0 

Thank you for your help.

Best,
L

[technolog · Accepted solution]

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.