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.
Hello. Can you help me, please ?
I need a table with the same column, with unique (not duplicate) values from IDU column ... filtered by DATE column.
I need the first row (date) of each IDU.
Thanks a lot
IDU | DATE | TRIM |
1 | 2/04/2021 | AAAA |
2 | 6/04/2021 | AAAA |
2 | 10/04/2021 | BBB |
2 | 14/04/2021 | BBB |
3 | 18/04/2021 | CCC |
4 | 22/04/2021 | CCC |
Solved! Go to Solution.
Here's the calculated table you want:
[New Table] = // This is a calculated table
// Let the table from your initial post
// be called T (you just substitute the
// name you've got in your model).
var IDUsWithFirstDates =
ADDCOLUMNS(
DISTINCT( T[IDU] ),
"@FirstDate",
CALCULATE(
MIN( T[Date] )
)
)
var Result =
CALCULATETABLE(
T,
TREATAS(
IDUsWithFirstDates,
T[IDU],
T[DATE]
)
)
RETURN
Result
Here's the calculated table you want:
[New Table] = // This is a calculated table
// Let the table from your initial post
// be called T (you just substitute the
// name you've got in your model).
var IDUsWithFirstDates =
ADDCOLUMNS(
DISTINCT( T[IDU] ),
"@FirstDate",
CALCULATE(
MIN( T[Date] )
)
)
var Result =
CALCULATETABLE(
T,
TREATAS(
IDUsWithFirstDates,
T[IDU],
T[DATE]
)
)
RETURN
Result
@astronauta49 , Create a new measure for TRIM and use min for date if needed
new TRIM =
VAR __id = MAX ('Table'[Name] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Name] = __id )
CALCULATE ( max ('Table'[TRIM] ), VALUES ('Table'[Name] ),'Table'[Name] = __id,'Table'[Date] = __date )
thank you so much !!
Sorry, it took me a long time to reply. Thank you very much for your answer. I have not solved my problem yet. I don't understand the code. Do you need to put it in a new measure or column? I'll keep doing tests. Thanks
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 |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |