Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I know the way I way I phrase the question is very weird but I do not know how to describe what I need, so below is an example of what I have.
Issue :
The below is a column I have
Product Code | Event Time |
A | 1/1/2022 |
B | 1/1/2022 |
C | 1/1/2022 |
A | 2/1/2022 |
B | 2/1/2022 |
C | 2/1/2022 |
A | 3/1/2022 |
B | 3/1/2022 |
C | 3/1/2022 |
I need to add a column to specify the sequence of the moves according to event time for each product code.
I would need something looks like this:
Product Code | Event Time | Sequence |
A | 1/1/2022 | 1 |
B | 1/1/2022 | 1 |
C | 1/1/2022 | 1 |
A | 2/1/2022 | 2 |
B | 2/1/2022 | 2 |
C | 2/1/2022 | 2 |
A | 3/1/2022 | 3 |
B | 3/1/2022 | 3 |
C | 3/1/2022 | 3 |
I think I need to use the summarize function but I am unfortunately not proficient enough to make out the formula on my own,
I would appreciate any help.
Thank you so much.
Regards.
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new column.
Sequence CC =
COUNTROWS (
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE ( ALL ( Data ), Data[Product Code], Data[Event Time] ),
ORDERBY ( Data[Event Time], ASC ),
KEEP,
PARTITIONBY ( Data[Product Code] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new column.
Sequence CC =
COUNTROWS (
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE ( ALL ( Data ), Data[Product Code], Data[Event Time] ),
ORDERBY ( Data[Event Time], ASC ),
KEEP,
PARTITIONBY ( Data[Product Code] )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
OMG, thank you so much for your fast response.
Worked like a charm.
User | Count |
---|---|
102 | |
91 | |
84 | |
77 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |