Forum Discussion

Ritesh_Air's avatar
Ritesh_Air
Post Patron
6 years ago
Solved

Conditional Count with Omission

Hi Experts,

 

I have a unique problem where I want to exclude a row if my retailer shows in more than 1 row and I only want to keep the row with Program Desc as "Contractual"

 

Like in Below example:

 

Retailer 1, 2 and 6 have multiple rows. 

 

I want to only see 1 row each Program Desc as "Contractual".

 

How do I do that in DAX?

 

 

Output that I want to see:

 

Thanks,

Ritesh

 

Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey Ritesh_Air ,

     

    I'm wondering if it's possible that a retailer has multiple rows, but without having "contractual".

    Basically you can create a new calculated column that checks the number of rows and if the description is contractual and flag the rows accordingly. The condition is like this

    • if there is just one row, keep the row
    • if there are more rows, then flag the row if description is "contractual"

    this is my sample data:

     
     

    and this is the DAX statement:

    flag = 
var __retailer = 'Table'[Retailer]
var noofrows = COUNTROWS(FILTER(ALL('Table') , 'Table'[Retailer] = __retailer))
return
IF(noofrows = 1 
    , "keep"
    , IF('Table'[Program Desc] = "Contractual", "keep" , "omit")
)

    Then you can use the column "keep" to filter the records.

     

    Hopefully, this is what you are looking for.

     

    Regards,
    Tom

5 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    Hey Ritesh_Air ,

     

    I'm wondering if it's possible that a retailer has multiple rows, but without having "contractual".

    Basically you can create a new calculated column that checks the number of rows and if the description is contractual and flag the rows accordingly. The condition is like this

    • if there is just one row, keep the row
    • if there are more rows, then flag the row if description is "contractual"

    this is my sample data:

     
     

    and this is the DAX statement:

    flag = 
var __retailer = 'Table'[Retailer]
var noofrows = COUNTROWS(FILTER(ALL('Table') , 'Table'[Retailer] = __retailer))
return
IF(noofrows = 1 
    , "keep"
    , IF('Table'[Program Desc] = "Contractual", "keep" , "omit")
)

    Then you can use the column "keep" to filter the records.

     

    Hopefully, this is what you are looking for.

     

    Regards,
    Tom

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      TomMartens this looks good but one question, why  ALLEXCEPT cannot be used? Just curious.

       

       

      flag = 
var noofrows = CALCULATE ( COUNTROWS( 'Table' ) , ALLEXCEPT ( 'Table', 'Table'[Retailer]))
return
IF(noofrows = 1 
    , "keep"
    , IF('Table'[Program Desc] = "Contractual", "keep" , "omit")
)

       

       

    • Ritesh_Air's avatar
      Ritesh_Air
      Post Patron
      6 years ago

      TomMartens 

       

      Thanks Tom. It works perfectly. Just one question, probably we couldn't have done it with measures, eh?

       

      Thanks again!

      • TomMartens's avatar
        TomMartens
        Super User
        6 years ago

        Hey Ritesh_Air ,

        you can create a measure that helps to decide if a row will be omitted or not.

         

        But this will become more complex, as a measure always has to be considered in the context of a visual, and the existing filter context that is "formed" by slicers, axis label, row/column header inside the visual.

         

        Regards,

        Tom