SOLVED

Dax or Power Query Challnege

%3CLINGO-SUB%20id%3D%22lingo-sub-1893550%22%20slang%3D%22en-US%22%3EDax%20or%20Power%20Query%20Challnege%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1893550%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20dear%20Friends%3CBR%20%2F%3EI%20have%20problem%20to%20applying%20a%20formula%20in%20Dax%20or%20Power%20Query%2C%26nbsp%3B%3CBR%20%2F%3EI%20have%20Some%20items%2C%20one%20list%20is%20for%20purchase%20and%26nbsp%3B%20Selling%20of%20our%20items%2C%20each%20recodr%20show%20our%20selling%2C%20and%20purchase%20quantity%20is%20repeated%2C%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3EWe%20have%20four%20header%20fro%20each%20items%2C%20Item%20name%2C%20Date%2C%20Purchase%20quantity%2C%20sales%20Quantity%20and%20Supplier%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ENow%20I%20need%20formula%20in%20DAX%20or%20Power%20Query%20to%20add%20one%20Column%20which%20it%20show%20me%20the%20number%20of%20Supplier%20for%20each%20items%2C%20For%20example%20for%20its%20%22%20Sweet%22%20we%20have%202%20supplier%20%2C%26nbsp%3B%3CBR%20%2F%3EAs%20I%20showed%20the%20table%20and%20the%20result%20which%20i%20want%20in%20Excel%20Sheet%2C%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20helping%20in%20advance%20%2C%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1893550%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1893631%22%20slang%3D%22en-US%22%3ERe%3A%20Dax%20or%20Power%20Query%20Challnege%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1893631%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F244734%22%20target%3D%22_blank%22%3E%40Jalal_1988%3C%2FA%3E%26nbsp%3BDAX%20is%20not%20my%20speciality.%20Therefore%2C%20I'm%20offering%20you%20a%20PQ%20solution.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1893698%22%20slang%3D%22en-US%22%3ERe%3A%20Dax%20or%20Power%20Query%20Challnege%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1893698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F244734%22%20target%3D%22_blank%22%3E%40Jalal_1988%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20you%20may%20add%20another%20column%20with%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(IF(%5BItem%5D%3D%5B%40Item%5D%2C1%2FCOUNTIFS(%5BItem%5D%2C%5B%40Item%5D%2C%5BSupplier%5D%2C%5BSupplier%5D)%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi dear Friends
I have problem to applying a formula in Dax or Power Query, 
I have Some items, one list is for purchase and  Selling of our items, each recodr show our selling, and purchase quantity is repeated,  
We have four header fro each items, Item name, Date, Purchase quantity, sales Quantity and Supplier,


Now I need formula in DAX or Power Query to add one Column which it show me the number of Supplier for each items, For example for its " Sweet" we have 2 supplier , 
As I showed the table and the result which i want in Excel Sheet, 

Thank you for your helping in advance ,

2 Replies
Highlighted

@Jalal_1988 DAX is not my speciality. Therefore, I'm offering you a PQ solution. See attached.

Highlighted
Best Response confirmed by Jalal_1988 (Contributor)
Solution

@Jalal_1988 

Or you may add another column with formula

=SUMPRODUCT(IF([Item]=[@Item],1/COUNTIFS([Item],[@Item],[Supplier],[Supplier]),0))