Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi community,
I've been searching the forum and the net for quite some time but am obviously being thick in how to look for an answer.
Assuming I have a table as below: I want to have a calculated column (either in DAX or M) that will count how many times the value in the ABN column exists across separate Vendor IDs.
Vendor ID | Bank | ABN | Count of Vendor with duplicate ABN |
AA | 1 | 115566 | 2 |
AA | 2 | 115566 | 2 |
AB | 3 | 115500 | 2 |
AC | 4 | 115566 | 2 |
AD | 5 | 115500 | 2 |
In essence, the fact that Vendor ID has the same ABN across two lines is normal (and irrelevant), what I want is to flag that another Vendor ID has the same ABN. Same for vendor AB, I want to flag that vendor AD has the same ABN.
THe intent here is to provide a table with all Vendor IDs that have duplicate ABN, so I'd be ok with a new table showing this.
Thanks in advance for your help.
BBK
Solved! Go to Solution.
Hi @billybazinga ,
I have a solution with just the Power Query editor, although depending on the size of your dataset it might not be the best performance wise:
Alternatively you could try referencing your original query instead of duplicating. This way you wont be able to merge it back, but you could load both tables and then use LOOKUPVALUES in a calculated column to get the count into your original table. I am honestly not sure which method would be better for performance.
I hope this helps, let me know if you have any questions.
Proud to be a Super User! | |
Hi, @billybazinga ,
The following DAX might work for you:
Count of Vendor with duplicate ABN =
CALCULATE(DISTINCTCOUNT('Table'[Vendor ID]),
ALLEXCEPT('Table','Table'[ABN])
)
The final output is shown in the following figure:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @billybazinga ,
I have a solution with just the Power Query editor, although depending on the size of your dataset it might not be the best performance wise:
Alternatively you could try referencing your original query instead of duplicating. This way you wont be able to merge it back, but you could load both tables and then use LOOKUPVALUES in a calculated column to get the count into your original table. I am honestly not sure which method would be better for performance.
I hope this helps, let me know if you have any questions.
Proud to be a Super User! | |