create new table with condition

[Pikachu-Power]

hi all,

i have a table that looks like following:

i want to create a new table with the restrictions name1 = X and name2 = Y and I want to see the id_2 with MIN(id):

Restriction for name is not a problem:

FILTER(Table, Table[name1] = "X" && Table[name2] = "Y")

Then I tried to identify the MIN cases:

New_Table =

VAR _SUMMARIZE = (FILTER(Table, Table[name1] = "X" && Table[name2] = "Y"),

Table[id_2],

"min_id", MIN(Table[id]))

RETURN

?

For Return I am looking for something like the the restricted table with id IN {min_id}

But maybe there are a better way to handle this MIN cases?

Thanks.

[parry2k · Accepted solution]

@Pikachu-Power update your expressions as below, I showed how to add days, you can use the same logic for start and end date

New_Table = 
ADDCOLUMNS(
    SUMMARIZE (
        FILTER(
            'Table', 
            'Table'[name1] = "X" && 
            'Table'[name2] = "Y"
        ),
        'Table'[Name1],
        'Table'[Name2],
        'Table'[id_2],
        "min_id", MIN ( 'Table'[id] )
    ),
    "days", var __id = [min_id] return CALCULATE ( min ( 'Table'[days] ), 'Table'[id] = __id )
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

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

[Pikachu-Power]

Hello,

I also need the days in the new_table. But only the days for MIN(Table[id]) of course. When i add days to the new_table it expands again to the double ids.

in additon: i forgot the start_date and end_date would also be necessary for MIN(Table[id])

I need something like:

New_Table =

VAR1 _SUMMARIZE = (FILTER(Table, Table[name1] = "X" && Table[name2] = "Y"),
Table[Name1],
Table[Name2],
Table[id_2],
"min_id", MIN(Table[id]))

VAR2 FILTER(Table, Table[name1] = "X" && Table[name2] = "Y")

RETURN
FILTER(VAR2, VAR2(id) IN {VAR1(min_id)})

[parry2k]

@Pikachu-Power you need this, although not sure why you want to create a new table when you can visualize the data without creating a new table, anyhow here it what you need for the new table

New_Table =
VAR _SUMMARIZE = (FILTER(Table, Table[name1] = "X" && Table[name2] = "Y"),
Table[Name1],
Table[Name2],
Table[id_2],
"min_id", MIN(Table[id]))

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

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