The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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]
)
It works, but I don't understand why I have some resources that are duplicate or more...
Could you help also for this aspects?
Hi,
I think your use-case is different than what you described in your initial question.
Please share your sample pbix file.
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
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]
)
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]
)
User | Count |
---|---|
83 | |
83 | |
37 | |
34 | |
32 |
User | Count |
---|---|
92 | |
79 | |
61 | |
51 | |
51 |