Forum Discussion

Pikachu-Power's avatar
Pikachu-Power
Impactful Individual
4 years ago
Solved

create new table with condition

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.

 
Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

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

  • Pikachu-Power's avatar
    Pikachu-Power
    Impactful Individual
    4 years ago

    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's avatar
    parry2k
    Super User
    4 years ago

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