New Table from existing table using filters

[Anonymous]

Hi all,

I was looking for a solution in existing thread but I didn't find nothing.

I want to create a new table that take a distinct value from a column and then take cost considering the last period from another column

Resource	Cost	Period
abc	10	FEB 2021
cbd	25	DEC 2021
sad	35	DEC 2021
cds	21	FEB 2021
cbd	25	FEB 2021
sad	15	JAN 2022
sad	50	FEB 2022
abc	13	JAN 2022
Distinct resource	Cost	Last period
abc	13	JAN 2022
cbd	25	DEC 2021
sad	50	FEB 2022
cds	21	FEB 2021

I don't know if the better solution is:

-use Power Query

-use new table function in Power BI

I tried the last one but the code doesn't work....

Could you please help me? also linking a previously thread

Many thanks

[Anonymous]

It works, but I don't understand why I have some resources that are duplicate or more...

Could you help also for this aspects?

[Jihwan_Kim]

Hi,

I think your use-case is different than what you described in your initial question.

Please share your sample pbix file.

[Anonymous]

Yes, i am an idiot.

in my data you can find this situation:

Resource	Cost	Period
abc	10	FEB 2021
cbd	25	DEC 2021
sad	35	DEC 2021
cds	21	FEB 2021
cbd	25	FEB 2021
sad	15	JAN 2022
sad	50	FEB 2022
abc	13	JAN 2022
sad	50	FEB 2022
cds	21	FEB 2021
abc	13	JAN 2022

there are more resources with the same cost and the same period.

Sorry again

[Jihwan_Kim · Accepted solution]

Hi,

I think the reason is because there are some rows that shows the same information (duplicated rows).

If you want to keep the duplicated rows, please try the below.

Please check the below picture and the attached pbix file.

New table Two = 
VAR lastperiod =
    GROUPBY (
        Data_Two,
        Data_Two[Resource],
        "@lastperiod", MAXX ( CURRENTGROUP (), Data_Two[Period] )
    )
VAR filterbylastperiod =
    CALCULATETABLE (
        Data_Two,
        TREATAS ( lastperiod, Data_Two[Resource], Data_Two[Period] )
    )
RETURN
    SUMMARIZE (
        SELECTCOLUMNS (
            filterbylastperiod,
            "Distinct resource", Data_Two[Resource],
            "Cost", Data_Two[Cost],
            "Last period", Data_Two[Period]
        ),
        [Distinct resource],
        [Cost],
        [Last period]
    )

[Jihwan_Kim]

Hi,

One of the ways to solve this is to have the Period column as Date Type, and try to write the below to create a new table.

Please check the below picture and the attached pbix file.

New table =
VAR lastperiod =
    GROUPBY (
        Data,
        Data[Resource],
        "@lastperiod", MAXX ( CURRENTGROUP (), Data[Period] )
    )
VAR filterbylastperiod =
    CALCULATETABLE ( Data, TREATAS ( lastperiod, Data[Resource], Data[Period] ) )
RETURN
    SELECTCOLUMNS (
        filterbylastperiod,
        "Distinct resource", Data[Resource],
        "Cost", Data[Cost ],
        "Last period", Data[Period]
    )