Filter expression in Calculate to only pull earliest date

Anonymous — Wed, 19 May 2021 17:19:10 GMT

This seems like it should be easy but I'm having trouble with syntax. I have a summary table and detail table that are joined together by an ID (1:*). In my visual, I want to show items from the summary table, with the Earliest PromiseDate from the detail table. No problem - built-in PBI capability. Now, I also want to show the concatenated BuyerID for all the detail rows where the PromiseDate = Earliest PromiseDate. I'm not sure the right syntax to filter this appropriately. Ideas?

Here is the measure I tried on my summary table, but I get the error "A function MIN has been used in a True/False expression that is used as a table filter expression. This is not allowed."

BuyerID Concat = CALCULATE(CONCATENATEX(DISTINCT('Detail'[BuyerID]),'Detail'[BuyerID],", "),MIN('Detail'[PromisedDate]))

The concatenate part works perfectly, except it shows up in the total line.

Re: Filter expression in Calculate to only pull earliest date

Anonymous — Wed, 19 May 2021 17:30:58 GMT

BuyerID Concat = CALCULATE( CONCATENATEX( DISTINCT( 'Detail'[BuyerID] ), 'Detail'[BuyerID], ", " ), FIRSTDATE( 'Detail'[PromisedDate] ) )

Filters under CALCULATE must always be tables (logical conditions of the form T[Col] = Value are converted into tables under the hood and are what's called syntactic sugar). MIN returns a scalar. FIRSTDATE returns a table.

Depending on your requirements you might need to wrap FIRSTDATE(....) in KEEPFILTERS.

topic Filter expression in Calculate to only pull earliest date in DAX Commands and Tips

Filter expression in Calculate to only pull earliest date

Re: Filter expression in Calculate to only pull earliest date