Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |