cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smileamile4
Helper I
Helper I

Filter expression in Calculate to only pull earliest date

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.

1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

 

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.

View solution in original post

1 REPLY 1
daxer
Solution Sage
Solution Sage

 

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors