Count of units not in certain conditions

[Anonymous]

"Part Number Master" has all part numbers plus various information about each.

"OnHand" has a list of all part numbers that I have a quantity of one or more.. (One part number can be listed various time in various conditions)

I want a distinct count of all "Part Number Master(Part Number)" where "Part Number Master(Assortment)="Y" and "OnHand(Condition) does not equal "NE" or "FN" or "OH".

Basically, I have 119 units that are in my assortment("Y"), and I need to know how many of the 119 that I don't have in "NE" or "FN" or "OH" conditions.

Keep in mind that if I don't have any of the part number in one of those conditions, it may not even appear on the "OnHand" table.  It may appear on the "OnHand" table if I have it in a different condition code.

Thanks for your help.

[v-yulgu-msft]

@Anonymous

In your scenario, you can create a measure to calculate the distinct count value for “Part Number Master(Part Number)”

DistinctCount = CALCULATE(DISTINCTCOUNT(OnHand[Part Number]),FILTER(RELATEDTABLE(OnHand),OnHand[Condition]<>"FN"),FILTER(RELATEDTABLE(OnHand),OnHand[Condition]<>"OH"),FILTER(RELATEDTABLE(OnHand),OnHand[Condition]<>"NE"))

Add this new measure directly to your table visual.

[Anonymous]

Thank you for your response.  I am currently out of 12 units from the 119 total.  Your formula is giving a result of "112".

It appears that your formula will return a count of part numbers that don't have in "NE", "FN" and "OH" condition so the 112 unit return is not the number I'm looking for and I don't know why it is not returning 12.  I modified both my "Part Number Master" table and  my "OnHand" table to only return results on part numbers that are in my assortment to eliminate possible issues.  I have the list of the twelve part numbers that I am out of but when I change the Values to a distinct count, the total goes up to 117, instead of the 12 listed when it is not a count.

The "On Hands" is a measure I created that counts only units that are in "FN", "OH" and "NE" conditions.

[Anonymous]

I'm still trying to figure this out. I write SQL script so I can pretty much set my data up anyway I want. I just need to be able to keep track of how many part numbers I don't have in the three conditions.

[ankitpatira]

@Anonymous Use table visual to get count of the field and use filters to exclude values "NE", "FN" or "OH" and other filter to have assortment to "Y". This would be easy as tables are related.

[Anonymous]

Thanks for the feedback. What I'm looking for is a distinct count of units I'm out of. I have on box titled "Total Assortment" and the result is 119. I need a box below that one titled "Total Out of Stock" and that should currently be 5. Out of the 119 units in my assortment, there are 5 units that I don't have in either "NE" , "FN", or "OH" conditions.