Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I am looking for a solution for the following situation:
I have the following table (Date and calculated column Seq) with a linked DateTable. I would like to have the highest value displayed for each group. The empty fields between the number sequences separate the individual groups. To illustrate this, there is the column "result".
Maybe you could create another column with the groups A,B,C (column „Groups“)etc. and filter them?
Date | Seq | result | Groups |
02/05/2019 |
|
|
|
02/06/2019 | 1 |
| A |
02/09/2019 | 2 | 2 | A |
02/10/2019 |
|
|
|
02/11/2019 | 1 |
| B |
02/12/2019 | 2 |
| B |
02/13/2019 | 3 |
| B |
02/16/2019 | 4 | 4 | B |
02/17/2019 |
|
|
|
02/18/2019 |
|
|
|
02/19/2019 | 1 | 1 | C |
02/20/2019 |
|
|
|
02/23/2019 | 1 |
| D |
02/24/2019 | 2 |
| D |
02/25/2019 | 3 | 3 | D |
I would appreciate any help!
Regards,
hwoehler
Solved! Go to Solution.
Hi Gordonlilj,
thank you very much for your quick reply.
Sorry, I forgot to make it clear that the "Groups" column does not exist yet. This would have to be created or it must be recognized how the groups are composed. That's my problem right now. Regards, hwoehler
It's not easy to generate the Group column using power query or dax, so I would suggest you use python code to get the Group column, you may refer to the code below:
# 'dataset' holds the input data for this script
import pandas as pd
Seq= dataset['Seq'].values.tolist()
Group = []
start = 0
for ele in Seq:
if ele == 1:
start = start + 1
Group.append(start)
else:
Group.append(start)
dataset['Group'] = Group
Then create a custom column using M code below to beautify the Value.Group column:
if [Value.Seq] <> "" and [Value.Group] = "1" then "A" else if [Value.Seq] <> "" and [Value.Group] = "2" then "B" else if [Value.Seq] <> "" and [Value.Group] = "3" then "C" else if [Value.Seq] <> "" and [Value.Group] = "4" then "D" else null
After that, remove the useless columns and create a calculate column using dax below:
Result = CALCULATE(MAX('Table'[Seq]), ALLEXCEPT('Table', 'Table'[Group]))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It's not easy to generate the Group column using power query or dax, so I would suggest you use python code to get the Group column, you may refer to the code below:
# 'dataset' holds the input data for this script
import pandas as pd
Seq= dataset['Seq'].values.tolist()
Group = []
start = 0
for ele in Seq:
if ele == 1:
start = start + 1
Group.append(start)
else:
Group.append(start)
dataset['Group'] = Group
Then create a custom column using M code below to beautify the Value.Group column:
if [Value.Seq] <> "" and [Value.Group] = "1" then "A" else if [Value.Seq] <> "" and [Value.Group] = "2" then "B" else if [Value.Seq] <> "" and [Value.Group] = "3" then "C" else if [Value.Seq] <> "" and [Value.Group] = "4" then "D" else null
After that, remove the useless columns and create a calculate column using dax below:
Result = CALCULATE(MAX('Table'[Seq]), ALLEXCEPT('Table', 'Table'[Group]))
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
If i understood you correctly then creating a calculated column using somthing like the code below should give the result you illustrated
Hi Gordonlilj,
thank you very much for your quick reply.
Sorry, I forgot to make it clear that the "Groups" column does not exist yet. This would have to be created or it must be recognized how the groups are composed. That's my problem right now. Regards, hwoehler
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |