Finding the new value of a column by year

[evvx234]

Hey everyone, 

I have a rather simple task I want implement in PowerBI. I am new to PowerBI and I am struggling with fitting the data types together. I have three columns of IDs, years and sales type (lets say fuel for the example). I want transform the data such that it has only the new type that the ID has bought that year. 

ID Type year
FuelProd_1 Gasoline 2020
FuelProd_1 Gasoline 2021
FuelProd_1 Diesel 2020
FuelProd_1 Diesel 2021
FuelProd_1 Diesel 2022
FuelProd_1 Hydrogen 2022
FuelProd_1 Hydrogen 2022
FuelProd_2 Gasoline 2020
FuelProd_2 Gasoline 2020
FuelProd_2 Gasoline 2020
FuelProd_2 Gasoline 2020
FuelProd_2 Gasoline 2022
FuelProd_2 Hydrogen 2022
FuelProd_2 Gasoline 2022

Becomes

ID Type year
FuelProd_1 Gasoline 2020
FuelProd_1 Diesel 2020
FuelProd_1 Hydrogen 2022
FuelProd_2 Gasoline 2020
FuelProd_2 Hydrogen 2022

I have the pieces. I would run through the dataset by ID and by year, find the unique type value and merge all the cleaned sets back together. I can filter on ID and on Year and use the distinct function but the data types (tables and columns) don't fit together. Also if I create a new table for each ID I would have way to much tables. 

There ought to be a better way to handle this task for large new of ID's and Types. I'm not very familiar with the DAX language and it doesn't exactly function like other languages I am more used to. 

Thanks in advance 🙂 

[v-kongfanf-msft · Accepted solution]

Hi @evvx234 ,

You can try formula like below:

New_ =
VAR NewTable =
    SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Type] )
VAR Result =
    ADDCOLUMNS (
        NewTable,
        "Year",
            MINX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[ID] = EARLIER ( 'Table'[ID] )
                        && 'Table'[Type] = EARLIER ( 'Table'[Type] )
                ),
                [Year]
            )
    )
RETURN
    Result

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[LokambaTH]

Might be due to the presence of multiple values in the Type column for some combinations of ID and Year.

NewColumn =
CALCULATE (
CONCATENATEX (
VALUES ( Table ),
Table[Type],
","
),
ALLEXCEPT ( Table, Table[ID], Table[Year] )
)

[LokambaTH]

Hi, Use a similar logic to get the unique Type for each ID and Year.

NewColumn =
CALCULATE(
VALUES(Table[Type]),
ALLEXCEPT(Table, Table[ID], Table[Year])
)

[evvx234]

It gives an error: 'A table with multiple values ​​was specified when a single value was expected.'

I think because the new column doesn't have the dimensions of the original table.