DAX Summarize column and FILTER combined.-

[manuelfigueira]

Hello Everyone,

I have the DAX below which is basically a filtering to find the all PHC characters among a list of items but I would like to know how to summarize the column Invoice [Invoice number] as there are duplicate invoices number and I just need unique PHC invoice numbers in one column plus the other extra columns? I can't fingure out how to combine summarize column function with filter function,

Many thanks!!

FILTER('FACT_SAP', 'FACT_SAP'[Invoice Amount] <> 0 && FIND("PHC",'FACT_SAP'[Invoice Number],,0) <>0)

[manuelfigueira]

Hello @Pragati11 

Thanks for the feedback! 

Yeah sure, this is my whole DAX but I have and error saying "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Any idea on this? 

Many thanks!

Bridge  Invoice Number = SUMMARIZECOLUMNS('FACT_SAP'[Invoice Number],"Unique Invoice Number", FILTER('FACT_SAP_FY1921', 'FACT_SAP_FY1921'[Invoice Amount as per SAP] <> 0 && FIND("PHC",'FACT_SAP_FY1921'[Invoice GR SAP],,0) <>0))

[manuelfigueira]

Hi @Pragati11 

You are awsome! thanks a lot for this, it worked out for me very well. My final DAX was this one and seems to run well

Bridge  Invoice Number = SUMMARIZECOLUMNS('FACT_SAP'[Invoice SAP], FILTER('FACT_SAP', 'FACT_SAP'[Invoice Amount SAP] <> 0 && FIND("PHC",'FACT_SAP'[Invoice SAP],,0) <>0), "Invoice SAP", SUM(FACT_SAP[Invoice Amount SAP]),"Dates",MIN('FACT_SAP'[Posting Date]))

Thanks a lot!! 

[Pragati11 · Accepted solution]

Hi @manuelfigueira ,

The DAX which you have shared will output a new table. I think you are trying to do this as a new column therefore you are getting the error.

So, add a new table and then use this DAX expression:

Thanks,

Pragati

[Pragati11]

HI @manuelfigueira ,

Can you share your complete SUMMARISE dax function here please?

You will have to basically use the SUMMARISE function on your INVOICE NUMBER column so you get unique invoice numbers and then other extra columns with expressions. 

You can check the following link on Summarise function:

https://docs.microsoft.com/en-us/dax/summarize-function-dax

Thanks,

Pragati