How to calculate repeated ID and dates only once?

[Pbiuserr]

Hello,

My dataset looks like that. I'd need to calculate count of ID with earliest date excatly once and only for each ID.Status needs to be "B"

[v-easonf-msft]

Hi， @Pbiuserr 

Please try formula like:

Count =
CALCULATE (
    DISTINCTCOUNT ( Table[ID] ),
    FILTER ( ALLEXCEPT ( Table, Table[Date].[Month] ), Table[Status] = "B" )
)

If it doesn't work, please explain further your expected results

Best Regards,
Community Support Team _ Eason

[PaulDBrown]

If you need the earliest date by IP and Status, try:

Min date = CALCULATE (MIN(Table[Date]), ALLEXCEPT(Table, Table[IP], Table[Status]))

[PaulDBrown]

Try:

Min date = CALCULATE (MIN(Table[Date]), FILTER(ALLEXCEPT(Table, Table[IP]), Table [Status] = "B"))

[Pbiuserr]

Hi,

It gives me only earliest date from entire dataset. I'd like to count earliest date per ID with that status. Could you help me?

[PaulDBrown · Accepted solution]

Ok, I've tested this out. Since you have several rows with the same ID, CrashDate and Status, unless you add an index to differentiate these rows (or there's another field which renders each row unique), PBI will summarize these rows into a single row.

1) So, to keep your structure and show all the rows, I have added an index column to render each row unique. As I say, if there is a field in the data which makes each row unique, you can use that field instead of the Index field I've used in the measure.

I have worked on this sample dataset (including the Index column)
The measure:

Min date per ID single row =
VAR _MinDateID =
    CALCULATE (
        MIN ( FTable[CrashDate] ),
        FILTER ( ALLEXCEPT ( FTable, FTable[ID] ), FTable[Status] = "B" )
    )
VAR _SelID =
    MAX ( FTable[ID] )
VAR _MinRow =
    CALCULATE (
        MIN ( FTable[Index] ),
        FILTER ( ALL ( FTable ), FTable[CrashDate] = _MinDateID && FTable[ID] = _SelID )
    ) // IF you have a field which renders each row unique, use the field (you don't then need the index column)
VAR _Summary =
    SUMMARIZE (
        FILTER ( FTable, FTable[Index] = _MinRow && FTable[CrashDate] = _MinDateID ),
        FTable[ID],
        FTable[CrashDate]
    ) // Again, if you have the unique field, use it instead of the FTable[Index] field
RETURN
    IF ( COUNTROWS ( _Summary ) = 1, _MinDateID )

Which gets you this:

2) If you don't need to show all the rows, and the summary suffices (you don't need the index column), you can use this measure:

Min date by Id for "B" Status =
VAR _MinDateID =
    CALCULATE (
        MIN ( 'FTable'[CrashDate] ),
        FILTER ( ALLEXCEPT ( 'FTable', 'FTable'[ID] ), 'FTable'[Status] = "B" )
    )
VAR _Summary =
    SUMMARIZE (
        FILTER ( FTable, FTable[CrashDate] = _MinDateID ),
        FTable[CrashDate],
        [ID]
    )
RETURN
    IF ( COUNTROWS ( _Summary ) = 1, _MinDateID )

To get:

I've attached the sample PBIS file

[Pbiuserr]

Thanks! Very insightful and helpful. Turned out that your first measure along with MIN on dates is fine, but I guess you achieved the same result. Thank you very much!