Summarized table based on DISTINCT and multiple FILTERS

[RonaldvdH]

I need to summarize a table based on DISTINCT values from the column Taak[Taakset] and then the value from COMMENT.

And then filtered based on a certain GridzStatus and Comments <> BLANK()

But i'm unable to incorperate the DISTINCT VALUE in this formula, can anyone help me

test = 
   CALCULATETABLE(
   SUMMARIZE(
        Taak,
        Taak[Taakset],
        Taak[Comments]),
        Filter(Taak,Taak[Comments]<>BLANK() && Taak[GridzStatus]="On_HOLD"))

[DataNinja777]

Hi @RonaldvdH ,

Your issue comes from how SUMMARIZE works with filtering and distinct values. The best way to approach this is to first apply filtering on the table and then summarize the results. You can achieve this by defining a variable that filters out rows where Taak[Comments] is blank and Taak[GridzStatus] is not "On_HOLD". After filtering, you can use SUMMARIZE to extract the distinct values.

test = 
VAR FilteredTable = 
    FILTER(
        Taak, 
        Taak[Comments] <> BLANK() && Taak[GridzStatus] = "On_HOLD"
    )
RETURN
    SUMMARIZE(
        FilteredTable,
        Taak[Taakset],
        Taak[Comments]
    )

If your goal is to ensure that Taakset remains strictly distinct, meaning you do not want duplicate values based on different comments, you can use DISTINCT along with SELECTCOLUMNS to create a more refined table.

test =
VAR FilteredTable = 
    FILTER(
        Taak, 
        Taak[Comments] <> BLANK() && Taak[GridzStatus] = "On_HOLD"
    )
RETURN
    DISTINCT(
        SELECTCOLUMNS(
            FilteredTable,
            "Taakset", Taak[Taakset],
            "Comments", Taak[Comments]
        )
    )

This approach ensures that the filtering is applied before summarization, avoiding any unwanted duplicates in the final summarized table. Let me know if you need further refinements!

Best regards,

[RonaldvdH]

@DataNinja777 your formula looks good but i'm still getting duplicate values in Taak[Taakset]

the Comments are different so i'm guessing it doesnt see the Distinct part of the summarize

ON-HOLD comments = 
VAR FilteredTable = 
    FILTER(
        Taak, 
        Taak[Comments] <> BLANK() && Taak[GridzStatus] = "On_HOLD"
    )
RETURN
    DISTINCT(
        SELECTCOLUMNS(
            FilteredTable,
            "Taakset", Taak[Taakset],
            "Comments", Taak[Comments]
        )
    )

[DataNinja777]

Hi @RonaldvdH ,

You're right that the DISTINCT function applies to the entire row, meaning if Taakset has different Comments, it will still create separate rows. To ensure that each Taakset appears only once while aggregating all related Comments, you can use CONCATENATEX to combine the comments into a single field.

ON-HOLD comments = 
VAR FilteredTable = 
    FILTER(
        Taak, 
        Taak[Comments] <> BLANK() && Taak[GridzStatus] = "On_HOLD"
    )
RETURN
    ADDCOLUMNS(
        DISTINCT(SELECTCOLUMNS(FilteredTable, "Taakset", Taak[Taakset])),
        "Comments", CONCATENATEX(FILTER(FilteredTable, Taak[Taakset] = EARLIER([Taakset])), Taak[Comments], ", ")
    )

This approach first filters out the rows where Comments is blank and GridzStatus is "On_HOLD". Then, it ensures that Taakset remains distinct by selecting only unique values. To handle the Comments, ADDCOLUMNS is used to create a new column where CONCATENATEX gathers all comments associated with each Taakset, joining them with a comma and space. This way, each Taakset appears only once with its corresponding comments combined into a single text value. Let me know if this solution works for your dataset!

Best regards,

[RonaldvdH]

@DataNinja777 you are the best, that worked.

Last question....

I need an extra column from the table Taak but how do I add a third column to this formula ?

ON-HOLD comments = 
VAR FilteredTable = 
    FILTER(
        Taak, 
        Taak[Comments] <> BLANK() && Taak[GridzStatus] = "On_HOLD"
    )
RETURN
    ADDCOLUMNS(
       DISTINCT(SELECTCOLUMNS(FilteredTable, "Taakset", Taak[Taakset])),
       "HLD-code",Taak[omschrijving],
       "Comments", CONCATENATEX(FILTER(FilteredTable, Taak[Taakset] = EARLIER([Taakset])), Taak[Comments], " || ")
    )

this is the third column: "HLD-code",Taak[omschrijving], 

[RonaldvdH]

@DataNinja777 any ideas? or @danextian do you have any ideas?