"New Table" in PowerBI Desktop

[Anonymous]

I created a "New Table" in PowerBI Desktop off of an existing table:

Table =

Filter(

Summarize(ExistingTable, ExistingTable[UniqueID1], ExistingTable[UniqueID2], 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"))

Now I'm having an issue where it's pulling more than the minimum Start Date for a Name.

Why is it pulling more than the "Min(ExistingTable[StartDate])"?

EDIT:

Here is the "ExistingTable" - I only want the highlighted lines in the New Table:

I am currently getting this result:

It is giving me.. the first date of any changes/updates?

[parry2k · Accepted solution]

@Anonymous 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.⚡

[Anonymous]

@parry2k AMAZING!! Thank you!!! 

[parry2k]

@Anonymous 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.⚡

[Anonymous]

@parry2k 
is there a way to limit/specify which columns are returned (the real "ExistingTable" has about 100 columns. I need 11 of them.)

[parry2k]

@Anonymous 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.

[Anonymous]

@parry2k 
First row.

I want the value(s) from the earliest start date, ex:

[parry2k]

@Anonymous 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.⚡

[Anonymous]

@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.

[Anonymous]

@Greg_Deckler any helpful links you could throw my way?

[Greg_Deckler]

@Anonymous I would try using GROUPBY instead of SUMMARIZE in this instance as MINX(CURRENTGROUP(...)...) can be better than SUMMARIZE and MIN.

[Anonymous]

@Jihwan_Kim any suggestions?

[selimovd]

Hey @Anonymous ,

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"
    )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd 

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?

[selimovd]

@Anonymous 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"
    )
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@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.