DAX - Exclude a specific filter in a specific column, but keep all others

[mmh9119]

Hi all,

I have the following DAX measure:

test = CALCULATE(
    COUNTROWS(ma_out_full),
    FILTER(ALL(ma_out_full), ma_out_full[Update Timestamp] = "As of Jul 31, 2023")
    ALLEXCEPT ( ma_out_full, ma_out_full[Transfer Status] = "Case Closed")
    )

The goal is to filter the table called "ma_out_full" in two ways. One, the rows are only counted where the row value under the "Update Timestamp" column shows "As of Jul 31, 2023". Two, that all values under the "Transfer Status" column are included, except those rows where the value under this column shows "Case Closed". So Basically, count all rows where the time stamp is "As of Jul 31, 2023", and all rows that are NOT marked as "Case Closed".

I thought ALLEXCEPT might be the way to go for the second condition, but unfortunately, the above DAX gives a syntax error.

Could someone be kind enough to let me know the correct syntax to make the above DAX work correctly?

Thanks!

[eliasayyy]

test = CALCULATE(
    COUNTROWS(ma_out_full),
    FILTER(ALL(ma_out_full), ma_out_full[Update Timestamp] = "As of Jul 31, 2023",ma_out_full[Transfer Status] <> "Case Closed")
    )

to help you better understand

all means remove filter from all the table
allexcept means remove filter from all the table except a specified column 
allselected means removefilter from all columns selected

[mmh9119]

Thanks annonymous1999 for the prompt reply!

Unfortunately, when I tried running your code, I got a message saying "Too many arguments were passed for the FILTER function. The maximum argument count for the function is 2". So I tried adding an extra pair of paranthesis to see if that would fix the issue, but it didn't.

Hope this isn't a version issue.

Thanks again.

[eliasayyy · Accepted solution]

oh my bad instead of , use && 

test = CALCULATE(
    COUNTROWS(ma_out_full),
    FILTER(ALL(ma_out_full), ma_out_full[Update Timestamp] = "As of Jul 31, 2023" && ma_out_full[Transfer Status] <> "Case Closed")
    )

[mmh9119]

Perfect! That did the trick.

Thanks again!