Conditional Count with Omission

[Ritesh_Air]

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

[TomMartens · Accepted solution]

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

[Ritesh_Air]

@TomMartens 

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

Thanks again!

[TomMartens]

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

[parry2k]

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

[parry2k]

Thanks @TomMartensfor pointing, I fixed my measure, there was a typo. Cheers!!