Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
If anyone can help me, how to compare the values of one column, based on the date from another column? I have a column that is included IDs (I have 2 or several IDs per day), I need to find the IDs from each day and put a label for each ID category in another column.
in the below example: I have Run ID which for Oct 15th, I have 4 rows with 2 codes, I wanted to put the label in another column, 60 as the first running, 61 the second running.
thanks
Solved! Go to Solution.
Hi, @Vibration85
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a calculated column as below.
Column =
var tab =
SUMMARIZE(
ALL('Table'),
'Table'[Run ID],
'Table'[Date]
)
var newtab =
ADDCOLUMNS(
tab,
"Rank",
var re =
COUNTROWS(
FILTER(
tab,
[Date]=EARLIER('Table'[Date])&&
[Run ID]<EARLIER('Table'[Run ID])
)
)+1
return re&" Running"
)
return
MAXX(
FILTER(
newtab,
[Run ID]=EARLIER('Table'[Run ID])&&
[Date]=EARLIER('Table'[Date])
),
[Rank]
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Vibration85
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a calculated column as below.
Column =
var tab =
SUMMARIZE(
ALL('Table'),
'Table'[Run ID],
'Table'[Date]
)
var newtab =
ADDCOLUMNS(
tab,
"Rank",
var re =
COUNTROWS(
FILTER(
tab,
[Date]=EARLIER('Table'[Date])&&
[Run ID]<EARLIER('Table'[Run ID])
)
)+1
return re&" Running"
)
return
MAXX(
FILTER(
newtab,
[Run ID]=EARLIER('Table'[Run ID])&&
[Date]=EARLIER('Table'[Date])
),
[Rank]
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Awesome Allan @v-alq-msft
Many thanks, it was great and exactly what I was looking for. So clean and perfect. 😊
Best regards
Hi, @Vibration85
Check if this is what you need:
let
Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
ChangeType = Table.TransformColumnTypes(Source,{"Run_Datetime", type date}),
Custom1 = Table.Combine(Table.Group(ChangeType,"Run_Datetime",{"n",each Table.AddColumn(_,"Num",(x)=>Text.From(1+List.PositionOf(List.Distinct([Run ID]),x[Run ID]))&" Running")})[n])
in
Custom1
Sorry, I didn't notice you need DAX code😂 I'm not familiar with DAX, so let's wait for DAX expert to help you.
Thank you so much for your effort 😁
by the way, as my time is so limited, could you give me a hint on how to use this M language? 😉
I'm not familiar with M lang at all, a dummy on it.
FYI.
Hey dude, apart from everything, I put my efforts and learn how to use it and it did work, many thanks 🙂
Thanks Pal, @shaowu459
It was not working for me, 😁 as I had many changes before this modification in the advance editor and I think a little bit different in code structure with I saw in Power Query advance editor structure. anyway, thanks a lot. Hope anyone can help me with the DAX code.
Cheers