Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
"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.
@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.
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 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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |