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 created a "New Table" in PowerBI Desktop off of an existing table:
I am currently getting this result:
It is giving me.. the first date of any changes/updates?
Solved! Go to Solution.
@kressb you can wrap it in SELECTCOLUMNS function and list the columns you need.
Table 2 =
VAR __table = ADDCOLUMNS ( VALUES ( 'Table'[UniqueId2] ), "@MinDate", CALCULATE ( MIN ( 'Table'[Start Date] ) ) )
RETURN
SELECTCOLUMNS (
CALCULATETABLE (
'Table',
TREATAS ( __table, 'Table'[UniqueId2], 'Table'[Start Date] )
),
"UniqueId1", [UniqueId1],
"UniqueId2", [UniqueId2],
"Number1", [Number1]
)
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
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.
@kressb you can wrap it in SELECTCOLUMNS function and list the columns you need.
Table 2 =
VAR __table = ADDCOLUMNS ( VALUES ( 'Table'[UniqueId2] ), "@MinDate", CALCULATE ( MIN ( 'Table'[Start Date] ) ) )
RETURN
SELECTCOLUMNS (
CALCULATETABLE (
'Table',
TREATAS ( __table, 'Table'[UniqueId2], 'Table'[Start Date] )
),
"UniqueId1", [UniqueId1],
"UniqueId2", [UniqueId2],
"Number1", [Number1]
)
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
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.
@kressb try this to create a table
Table 2 =
VAR __table = ADDCOLUMNS ( VALUES ( 'Table'[UniqueId2] ), "@MinDate", CALCULATE ( MIN ( 'Table'[Start Date] ) ) )
RETURN
CALCULATETABLE (
'Table',
TREATAS ( __table, 'Table'[UniqueId2], 'Table'[Start Date] )
)
and here is the output
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
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.
@parry2k
is there a way to limit/specify which columns are returned (the real "ExistingTable" has about 100 columns. I need 11 of them.)
@kressb let me ask you this, in the case of 55555 where number 1 and number 2 values are different, which value you would like in that case, the value from the first row of 55555 or 2nd row of 55555.
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.
@kressb your request doesn't make sense, you are summarizing the data on so many columns and some of the columns have different values, and that is why it is returning two rows.
for example, for 55555, number 1 and number 2 have different values and you will get the two rows where for 66666 all the columns have the same values and you are getting one row.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
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.
@parryk2 the request is to pull the lines with the first (min) start date for each UniqueID2
the request makes sense, I just don't know how to code it appropriately.
Yes, I've already determined what it is doing. I just don't know why or how to fix the code to get it to do what I want.
@kressb I would try using GROUPBY instead of SUMMARIZE in this instance as MINX(CURRENTGROUP(...)...) can be better than SUMMARIZE and MIN.
Hey @kressb ,
calculations in SUMMARIZE can lead to wrong results. For that reason it's best practice to use SUMMARIZE only to summarize columns and do the calculations with ADDCOLUMS. Take a look at the following article for details:
Best Practices Using SUMMARIZE and ADDCOLUMNS - SQLBI
I don't know if that's the case here, but that's the first thing I would try.
In your case this would mean the formula should be:
Table =
FILTER(
ADDCOLUMNS(
SUMMARIZE(
ExistingTable,
ExistingTable[UniqueID1],
ExistingTable[UniqueID1],
ExistingTable[Name],
ExistingTable[Number1],
ExistingTable[Number2],
Rollforward[Number3],
ExistingTable[Number4],
ExistingTable[Number5],
ExistingTable[Notes],
ExistingTable[Site]
),
"StartDate", MIN( ExistingTable[Start Date] )
),
CONTAINSSTRING(
ExistingTable[Notes],
"Abc"
)
)
Adding the paranthesis after the Start Date:
"StartDate", MIN( ExistingTable[Start Date] )
defaults the data to the minimum start date in the entire table.
I need minimum start date for the unique id 2
maybe that is the flaw in my setup
is there a way to write it so it 's just the first start date for each uniqueid2?
@kressb Sorry, forgot about the context transition. In this case the MIN needs a CALCULATE:
"StartDate", CALCULATE( MIN( ExistingTable[Start Date] ) )
Or here the whole measure:
Table =
FILTER(
ADDCOLUMNS(
SUMMARIZE(
ExistingTable,
ExistingTable[UniqueID1],
ExistingTable[UniqueID1],
ExistingTable[Name],
ExistingTable[Number1],
ExistingTable[Number2],
Rollforward[Number3],
ExistingTable[Number4],
ExistingTable[Number5],
ExistingTable[Notes],
ExistingTable[Site]
),
"StartDate", CALCULATE( MIN( ExistingTable[Start Date] ) )
),
CONTAINSSTRING(
ExistingTable[Notes],
"Abc"
)
)
@selimovd No sorry, I wish it was that easy.
I think the reason I am getting two start dates is the Unique ID 1 is changing from the first month to the second month.
I need a formula where it just pulls the data for the First Start Date, no matter if there are changes later.
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |