Distinct count measure - if 2 lines are the same choose the max value

[Anonymous]

Hi everyone! I have a measure, see below, - but it is not reporting counts correctly.

ProjectRollUp = var t=SUMMARIZE(filter(FullTable,FullTable[ProjectIsVendorSingleDOS]=TRUE),'FullTable'[Project],"ChartRollUp",DISTINCTCOUNT(FullTable[RollUpID]))
return
SUMX(t,[ChartRollUp])

In this situation, some lines may show several results over the course of a few days. Example below.

Project	Date	RollUpID
A	6/1/21	abc123
A	6/1/21	def456
A	6/3/21	abc123

So in my measure I want to see that on 6/3/21 - 'abc123' happened and ignore the 'abc123', 6/1/21 line. But I still want the 'def456' 6/1/21. The issue is when the RollUpID exists more than once (like with the 'abc123') I need to assign it in my matrix under the most recent date, 6/3/21. Hopefully this makes sense... TIA.

[Anonymous]

The assumption is that RollupID's are unique between projects, i.e., one project can't have the same RollupID's as another.

RollupID Count = 
var CurrentMaxDate = MAX( T[Date] )
var CurrentRollupIDs = DISTINCT( T[RollupID] )
var CountRollupIDs = 
    COUNTROWS(
        EXCEPT(
            CurrentRollupIDs,
            CALCULATETABLE(
                DISTINCT( T[RollupID] ),
                KEEPFILTERS( T[Date] > CurrentMaxDate ),
                ALLSELECTED( T[Date] )
            )
        )
    )
return
    CountRollupIDs

[Anonymous]

ProjectRollUp =
CALCULATE(
    SUMX(
        DISTINCT( T[Project] ),
        CALCULATE( DISTINCTCOUNT( T[RollUpID] ) )
    ),
    KEEPFILTERS( T[ProjectIsVendorSingleDOS] )
)

// or (both should return the same result
// but one could be faster than the other)

ProjectRollUp =
CALCULATE(
    SUMX(
        ADDCOLUMNS(
            // You should NEVER use SUMMARIZE
            // for anything else but grouping.
            // The SUMMARIZE function is so
            // complex that if you use it for
            // anything more, you are risking
            // calculating wrong figures before
            // you know it and you WON'T be able
            // to even spot it. Here's the article
            // that you should read:
            // https://www.sqlbi.com/articles/all-the-secrets-of-summarize/
            // And here's a horror story about
            // single-table models, so stay away
            // from them if you want to avoid 
            // scratching your head for weeks 
            // or even produce wrong number without
            // knowing it:
            // https://www.sqlbi.com/tv/auto-exist-on-clusters-or-numbers-unplugged-22/
            SUMMARIZE(
                T,
                T[Project]
            ),
            [@DistinctCount]
                CALCULATE(
                    DISTINCTCOUNT( T[RollUpID] )
                )
        ),
        [@DistinctCount]
    ),
    KEEPFILTERS( T[ProjectIsVendorSingleDOS] )
)

Mandatory:

1. https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

2. https://www.sqlbi.com/tv/auto-exist-on-clusters-or-numbers-unplugged-22/

[Anonymous]

This doesn't solve for the case where the RollUpID exists more than once (like with the 'abc123') I need to assign it in my matrix under the most recent date, 6/3/21 - I am getting the same results with your solution as my own using SUMMARIZE. I think I need to incorporate a conditional that says 'if this ID exists use max(date), or something like that.

[Anonymous]

You need to explain what you really want in a much clearer way, I'm afraid. My formula stems from my understanding. If I got it wrong, it's because the explanation is not clear enough.

[Anonymous]

Project	Date	RollUpID
A	6/1/21	abc123
A	6/1/21	def456
A	6/3/21	abc123

 see below desired results, the counts of one per day is the distinct count of the RollUpID per day - only the lines in red (above) would be counted because the ID 'abc123' defaults to the max(date).

Project	6/1/21 -	6/3/21
A	1	1

If you refer back to the original example above. If I put this into a Matrix visual with the Date as my columns field, the RollUpID 'abc123' is shown twice. Once under 6/1/21 and again under 6/3/21. I do not want to aknowledge/ count the line for 6/1/21, where RollUpID is 'abc123'. So I think I need to add a condition somewhere that will default to the MAX(Date) when the Project and RollUpID appear more than once.