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.
I have data something like this and I want get the first and last rows based on the date:
I created some measure for example like InitialScore, LastScore, FirstDate...:
InitialScore = MINX( FILTER( ALL(Sheet1), [CatIns] = EARLIER([CatIns]) && [Date] = EARLIER(Sheet1[InitialTestDate])), [Score])
And got results like this which is correct but..:
I have score2, 3 and more columns in the real data and I don't think should be creating measures for each column? Is there a way to get all the rows filtered by first and last row by Category (CatIns)?
Thanks,
Solved! Go to Solution.
HI @smerchant ,
You can consider to do unpivot columns on your fields, then you can simply write formula to calculate first/last records based on original category and attribute group.
Regards,
Xiaoxin Sheng
@smerchant measure or column will only return scalar value not a row
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks. I might have failed to explain. I need to get all the columns and only first and last row by category based on the date (first and lastest)
I made another attempt through an identifier column. Not sure if this is most appropriate way, although, I do get the empty row on top which I suppose I can filter out.
Identifier = IF ( MIN ( Sheet1[Date] ) = Sheet1[FirstTransactionDate], "First Test", IF ( MAX ( Sheet1[Date] ) = Sheet1[LastTransactionDate], "Last Test", BLANK () ) )
This attempt also give the wrong sum for score columns
@smerchant issue with your question is to get all columns in a row, it is possible to get a column, not a problem, and you have to perform the same calculation for all the columns.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks. Even if you have 20 columns? is it possible to create a seperate table based on the condition/filter which gets updated?
HI @smerchant ,
You can consider to do unpivot columns on your fields, then you can simply write formula to calculate first/last records based on original category and attribute group.
Regards,
Xiaoxin Sheng
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |