Count ID only if other column is not blank for all rows

[Anonymous]

I have a dataset full of "vendor IDs" that can be added and expired in different markets. I want to be able to track if a Vendor ID has been expired in all instances so with the screenshot below, the count would be zero because not all expiration dates are populated. If they were, I would want the count to be one. Is there a way to do this with DAX measures?

[Greg_Deckler · Accepted solution]

Hmm, assuming that we have vendor_id in the visualization:

Count Measure = 

  VAR __Count1 = COUNTROWS('Table')

  VAR __Count2= COUNTROWS(FILTER('Table',NOT(ISBLANK('Table'[expiration_date]))))

RETURN

  IF(__Count1 = __Count2,1,0)

For all vendor ID's you might have to do something like:

Count Measure = 

  VAR __Table = 

    SUMMARIZE(

      'Table',

      [vendor_id],

      "__Count1" = COUNTROWS('Table'),

      "__Count2" = COUNTROWS(FILTER('Table',NOT(ISBLANK('Table'[expiration_date]))))

  )

RETURN

  COUNTROWS(FILTER(__Table,[__Count1] = [__Count2]))

[Anonymous]

So that count is working for the complete total count, but doesn't work when monthly/daily filters are applied. I've been trying to calculate a running total of all vendors by day:

Total Vendor IDs =

CALCULATE(DISTINCTCOUNT('Plan Allocation Spread'[vendor_id]),

FILTER(

GROUPBY(ALLSELECTED('Plan Allocation Spread'),

'Plan Allocation Spread'[load_date],'Spread Calendar'[Date]),

'Plan Allocation Spread'[load_date]<=MAX('Spread Calendar'[Date])),

'Plan Allocation Spread'[expiration_date] = BLANK()

)

Is there a way to groupby in a similar way for the expired measure to capture the daily change?

[Anonymous]

Yes! This is pulling in the correct number I'm looking for.

Thanks so much!

[Greg_Deckler]

Great! 🙂

[amitchandak]

@Anonymous , Screenshot is missing but seems like a case of all except

Refer

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/