Measure: How do I make it ignore certain values in visual content?

[Mjolnir]

Hi,

Want to create a measure that sums the values if the account number starts with a number greater than 2.

All accounts with values equal to blank, empty or zero should be ignored; all accounts starting with 2 or less should be ignored. Ignored data should not appear in visual content. Currently I have accounts starting with 1*** and 2*** appearing in my tables, and =>3*** with blank/empty/zeroes appearing as well.

I thought the filter would ignore and not include in the visuals but it seems like it does not.

How can I fix this changing the measure? And not through applied filters to the visual.

mActual =

CALCULATE(

    FORMAT(

        SUM(general_ledger_facts[value]), "### ### ### ###"),

        LEFT(dim_account[accountnumber],1)*1>2)

[lbendlin]

mActual =
FORMAT(
CALCULATE(
        SUM(general_ledger_facts[value]),
        LEFT(dim_account[accountnumber],1)>"2"),
, "### ### ### ###")

[Mjolnir · Accepted solution]

Thanks for your response. I changed the SUM to SUMX and it solved the problem.

[Mjolnir]

In addition to my comment, sum and sumx alone seem to retrieve and only show accounts with existing values. The issue seemed to be caused by FORMAT, so removed the FORMAT in the function and all blanks disappeared.