Forum Discussion

markefrody's avatar
markefrody
Icon for Post Patron rankPost Patron
8 years ago

Using DAX: Removing Duplicates in Column A Before Summing Up Column B

Hi,   I would like to ask you assistance if there is a DAX query that could help me on the below. Please note that I cannot use remove duplicates in "Edit Queries" since I have other calculations t...
Need Help
MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago
Hi @markerfrody

Add a new table with the following syntax

Nem table =
SUMMARIZE (
ALL ( Table[Job: Name]; Table[SPHA'S Filled] );
"Job"; DISTINCT ( Table[Job: Name] );
"SPAS"; SUM ( Table[ SPHA'S Filled] )
)

Regards,
MFelix
  • markefrody's avatar
    markefrody
    Icon for Post Patron rankPost Patron
    8 years ago

    Hi MFelix, I used the DAX you provided but it is giving me this error:

    "A table of multiple values was supplied where a single value was expected."

     

    Here is the DAX query used:

     

    New Table =
    SUMMARIZE(

    ALL('SPHA Fulfillment'[Job: Name], 'SPHA Fulfillment'[SPHA's Filled]),
    "Job", DISTINCT('SPHA Fulfillment'[Job: Name]),
    "SPAS", SUM('SPHA Fulfillment'[SPHA's Filled])

    )

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      8 years ago

      Hi markefrody,

       

      Made an error on my formula if you use this one this will make distinct values of job name and Office:

       

      Table = 
SUMMARIZE (
    ALL ( Table1[Job:Name]; Table1[Office]; Table1[SPHA's FIlled] );
    Table1[Job:Name];
    Table1[Office];
    "SPHA"; SUM ( Table1[SPHA's FIlled] )
)

      In the summarize when you choose a column it will automatically give you the distinct value in a column so no need to do it again that's why it was giving you the error. See the result below:

       

       

      Regards,

      MFelix

      • markefrody's avatar
        markefrody
        Icon for Post Patron rankPost Patron
        8 years ago

        Thanks for your assistance MFelix. It seems that what I'm looking for is a bit more complex than I expected. I posted a new post explaining the output I expect.