Need Help - Counting True/False total from a measure

[acarolina]

Hi All, 

I have a difficulty to calculate the total of True/False from measures. FYI, there is no column here.

StatusChange = IF([Expat HC M-1]=1 && [Non-Expat HC CM]=1, -1, BLANK())

[Expat HC M-1]=1 will filter employees that are considered expat in Month-1 (M-1)

[Non-Expat HC CM]=1 will filter employees that are considered Non-expat in Current Month (CM)

So I want to flag those that have changed status from expat (a month before) to non-expat (current month).
The measure works correctly. 

However, I cannot generate the total SUM for those StatusChange, it doesnt count 5 or 10 or 15 for example. It only shows the list of people under those flag. 

Could you please help me?

[Anonymous · Accepted solution]

Hi @acarolina 

When working with DAX measures in Power BI, it's important to remember that measures calculate values dynamically based on the current context established by filters, rows, and columns in your report. In your case, you've created a measure, , that evaluates to either -1 or BLANK() depending on whether an employee has changed status from expat to non-expat between two time periods. However, you're facing an issue when trying to aggregate this measure across multiple rows because you're essentially trying to count occurrences of a condition rather than summing numerical values directly associated with each row in a table.

Suggested Measure for Counting Status Changes

StatusChangeCount = 
SUMX(
    VALUES(EmployeeTable[EmployeeID]), 
    IF(
        [Expat HC M-1] = 1 && [Non-Expat HC CM] = 1,
        1,
        0
    )
)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[acarolina]

You are my savior! thanks a lot... it worked!

[lbendlin]

You cannot measure a measure directly. You need to materialize it first.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...