Summarise Columns reduces total amount of items

[PJVisscher89]

Hi, I am running into the following problem.

I have a table with the day-to-day status of Azure DevOps PBIs, so there are multiple rows with the same ID and date columns contain (many) duplicates of dates.

To run several analyses, I am using the summarisecolumns function to create a table with just 1 row per PBI. As soon as I add the column with closed date, the total amount of PBIs goes from 27.591 to 22.194. The column closed date is based on the following measure:

AR_ClosedDate =

IF(

    ISBLANK(

        MAX(AR_Analytics[Closed Date])),

    BLANK(),

    MAX(AR_Analytics[Closed Date])

    )

The issue may be somewhere in this measure, but I can't figure it out.

EDIT: Adjusted my ClosedDate measure to the below but that resulted in the same amount of lines:

    VAR WorkItem = SELECTEDVALUE(AR_Analytics[Work Item Id])

    RETURN

    MAXX(

        FILTER(

        ALL(AR_Analytics),

        AR_Analytics[Work Item Id] = WorkItem ),

    AR_Analytics[Closed Date]

    )

--

In my test table I used the below measure and it worked fine with the summarised table but when I transferred this measure to use on the main table I got the error that you can't use LASTDATE on a column with duplicate dates -- even though in my test table there are plenty duplicates to be found in every date column.

IF(

    ISBLANK(

        CALCULATE(

            LASTDATE(Trial_Test_Table[Closed Date]),

            FILTER(Trial_Test_Table, Trial_Test_Table[Closed Date] > DATE(2023,12,31)))),

    BLANK(),

        CALCULATE(

            LASTDATE(Trial_Test_Table[Closed Date]),

            FILTER(Trial_Test_Table, Trial_Test_Table[Closed Date] > DATE(2023,12,31)))

    )

[Anonymous · Accepted solution]

Hi @PJVisscher89 ,

The SUMMARIZECOLUMNS function is designed to create a summary table over a set of groups, and it only includes rows for which at least one of the supplied expressions returns a non-blank value. When you add the Closed Date column, it’s possible that the function is filtering out PBIs that do not have a closed date, hence reducing the total count.

The MAX function should return the maximum value in the column, but if there are blank values, it might be causing unexpected behavior. Similarly, using LASTDATE on a column with duplicates can lead to errors because LASTDATE expects a single value for each context, and duplicates create multiple values.

So I think you can change your DAX code.

AR_ClosedDate =
VAR LastClosedDate =
    CALCULATE (
        MAX ( AR_Analytics[Closed Date] ),
        ALL ( AR_Analytics ),
        AR_Analytics[Work Item Id] = EARLIER ( AR_Analytics[Work Item Id] )
    )
RETURN
    IF ( ISBLANK ( LastClosedDate ), BLANK (), LastClosedDate )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PJVisscher89]

Thanks, that's very helpful in understanding how those are processed and where things were going wrong. I think I've been able to use that to now resolve it in a proper manner

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...