Forum Discussion

markefrody's avatar
markefrody
Post 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 that I am using for that table.

 

I'm trying the following steps in this order:

1.) Temporary remove duplicate records in column "Job: Name" in order to do step #2 

2.) Add up all the numbers found in column "SPHA's Filled" based on "Job: Name".

3.) Group those numbers in step #2 based on "Office".

Note: I want to retain all the fields in the table not just "Job: Name", "SPHA's Filled", and "Office".

 

Please see the screenshot of the query I am working with:

File

 

Appreciate your kind assistance and please do let me know if you have further questions.

 

Best regards,
Mark V.

 

Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    By what criteria do you want to remove duplicates? For example, the two "debottleneck" entries in your screenshot both have one "SPHA Filled". Let's say they didn't both have one, but rather the first had 10, and the second had 5. Which one would you want to keep? Or do you not care/is it arbitrary? Or is "SPHA Filled" always one or zero?

    • markefrody's avatar
      markefrody
      Post Patron
      8 years ago

      Hi Anonymous,

       

      No criteria in removing duplicates. I just want it to be removed for the sake of counting all the numbers in "SPHA filled". If the removed entry has a number in "SPHA filled" it should not be counted.

  • MFelix's avatar
    MFelix
    Super 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
      Post 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
        Super 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi markefrody,

     

    You can create a new DAX table with this formula:

     

    NewDAXTable =
SUMMARIZECOLUMNS (
    'Table'[Job: Name],
    "Sum_SPHA_Filled", MAX ( 'Table'[SPHA's Filled] )
)

    Regards.

    • markefrody's avatar
      markefrody
      Post Patron
      8 years ago

      Hi Anonymous,

       

      Thanks for the suggestion. Is it posibble to group it also via "Office"?