Forum Discussion

mmh9119's avatar
mmh9119
Frequent Visitor
3 years ago
Solved

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

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!

Need Help
  • eliasayyy's avatar
    eliasayyy
    3 years ago

    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")
    )

4 Replies

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

     

     

    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's avatar
      mmh9119
      Frequent Visitor
      3 years ago

      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's avatar
        eliasayyy
        Icon for Memorable Member rankMemorable Member
        3 years ago

        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")
    )