Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi all,
I have a solution that needs to integrate manual updates during downtimes before eventually reconciling the data once the correct information becomes available at source.
The solution needs to:
Considering the attached table, I’ve tried to outline the logic I’m trying to implement below:
For the purposes of this explanation, I’ll refer to the manual adjustment as row X.
IF Flag = “Adj” (
IF Case = “L” (
For any Date >= Than X.Date WHERE ID = X.ID And Case != X.Case
Output = “H”
)
IF Case = “M” (
For any Date >= Than X.Date WHERE ID = X.ID And Case != X.Case
Level = X.Level
)
)
Any advice on how to optimally achieve this within PowerBI would be greatly appreciated.
IDCaseFlagLevelDateOutput
1 | HC | Source | 1 | 03/07/2024 | S |
1 | HC | Source | 1 | 04/07/2024 | S |
1 | L | Adj | 1 | 05/07/2024 | S |
1 | HC | Source | 1 | 06/07/2024 | S |
1 | HC | Source | 1 | 07/07/2024 | S |
1 | L | Source | 1 | 08/07/2024 | S |
1 | HC | Source | 1 | 09/07/2024 | S |
1 | HC | Source | 1 | 10/07/2024 | S |
1 | HC | Source | 1 | 11/07/2024 | S |
1 | HC | Source | 1 | 12/07/2024 | S |
2 | HC | Source | 3 | 03/07/2024 | S |
2 | HC | Source | 3 | 04/07/2024 | S |
2 | HC | Source | 3 | 05/07/2024 | S |
2 | HC | Source | 3 | 06/07/2024 | S |
2 | HC | Source | 3 | 07/07/2024 | S |
2 | L | Source | 3 | 08/07/2024 | S |
3 | HC | Source | 3 | 03/07/2024 | S |
3 | HC | Source | 3 | 04/07/2024 | S |
3 | HC | Source | 3 | 05/07/2024 | S |
3 | HC | Source | 3 | 06/07/2024 | S |
3 | HC | Source | 3 | 07/07/2024 | S |
3 | L | Adj | 3 | 08/07/2024 | S |
3 | HC | Source | 3 | 09/07/2024 | S |
3 | HC | Source | 3 | 10/07/2024 | S |
3 | HC | Source | 3 | 11/07/2024 | S |
3 | HC | Source | 3 | 12/07/2024 | S |
4 | HC | Source | 5 | 03/07/2024 | S |
4 | HC | Source | 5 | 04/07/2024 | S |
4 | L | Adj | 5 | 05/07/2024 | S |
4 | HC | Source | 5 | 05/07/2024 | S |
4 | HC | Source | 5 | 06/07/2024 | S |
4 | HC | Source | 5 | 07/07/2024 | S |
4 | HC | Source | 5 | 08/07/2024 | S |
4 | HC | Source | 5 | 09/07/2024 | S |
4 | HC | Source | 5 | 10/07/2024 | S |
4 | HC | Source | 5 | 11/07/2024 | S |
4 | L | Source | 5 | 12/07/2024 | S |
5 | HC | Source | 2 | 03/07/2024 | S |
5 | M | Adj | 3 | 04/07/2024 | S |
5 | HC | Source | 2 | 04/07/2024 | S |
5 | HC | Source | 2 | 05/07/2024 | S |
5 | HC | Source | 2 | 06/07/2024 | S |
5 | HC | Source | 2 | 07/07/2024 | S |
5 | HC | Source | 2 | 08/07/2024 | S |
5 | M | Source | 3 | 09/07/2024 | S |
5 | HC | Source | 3 | 10/07/2024 | S |
5 | HC | Source | 3 | 11/07/2024 | S |
5 | HC | Source | 3 | 12/07/2024 | S |
5 | HC | Source | 3 | 13/07/2024 | S |
Solved! Go to Solution.
Hi @PaulBoden ,
You can create a calculated column as below to get it, please find the details in the attachment.
Output =
VAR _id = 'Table'[ID]
VAR _date = 'Table'[Date]
VAR _case = 'Table'[Case]
VAR _level = 'Table'[Level]
VAR _ladjdate =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
'Table',
'Table'[ID] = _id
&& 'Table'[Flag] = "Adj"
&& 'Table'[Case] = "L"
)
)
VAR _madjdate =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
'Table',
'Table'[ID] = _id
&& 'Table'[Flag] = "Adj"
&& 'Table'[Case] = "M"
)
)
RETURN
IF (
(
'Table'[ID] = _id
&& NOT ( ISBLANK ( _ladjdate ) )
&& 'Table'[Date] >= _ladjdate
&& 'Table'[Case] <> "L"
)
|| (
'Table'[ID] = _id
&& NOT ( ISBLANK ( _madjdate ) )
&& 'Table'[Date] >= _madjdate
&& 'Table'[Case] <> "M"
&& 'Table'[Level] = _level
),
"H"
)
Best Regards
Hi @PaulBoden ,
I'm not clear about your requirement. Do you want to get the field [Output]? Could you please explain the judgement logic base on your provided sample data?
Explain the judgement logic for it base on the sample data
Expected result is Output field?
Best Regards
Apologies for being unclear - The table should be formatted as below.
ID | Case | Flag | Level | Date | Output |
1 | HC | Source | 1 | 03/07/2024 | S |
1 | HC | Source | 1 | 04/07/2024 | S |
Hi @PaulBoden ,
Could you please explain the judgement logic of getting the field [Output] base on the shared sample data and below formula? For example: if xx="Adj" && xxx = xxx then xx
IF Flag = “Adj” (
IF Case = “L” (
For any Date >= Than X.Date WHERE ID = X.ID And Case != X.Case
Output = “H”
)
IF Case = “M” (
For any Date >= Than X.Date WHERE ID = X.ID And Case != X.Case
Level = X.Level
)
)
Best Regards
I'm sorry, I'm not sure I understand the question.
If a record has "[Flag] = "Adj" and [Case] = "L", I want to set [Output] ="H" for all other records with the same [ID] where [Date] > than the date value contained in this record and where [Case] != "L"".
The second part of the logic ([Case] != "L") is because eventually the golden source will update and a record will be ingested containing x.[ID], x.[Case] and [Flag] = Source.
Hopefully this helps clarify?
Someone else suggested the below to me:
let Source = Table.Sort(your_table, {"ID", "Date"}), rec = (x) => [L = {"Output", each "H"}, M = {"Level", each x{0}[Level]}], fx = (tbl) => Table.TransformColumns(tbl, Record.FieldOrDefault(rec(tbl), tbl{0}[Case], {})), group = Table.Group( Source, {"ID", "Case"}, {"x", fx}, GroupKind.Local, (s, c) => Number.From(s[ID] <> c[ID] or List.Contains({"L", "M"}, c[Case])) ), z = Table.Combine(group[x]) in z
This seems to achive close to the outcome I'm looking for, although I suspect for performance optimisation, it would be better to execute this as a step within the existing query, rather than a standalone. Additionally, the outputs seem slightly different from what I'm expecting. I wonder if it's because the flag logic isn't considered and the rules are applied to all records regardless.
If you have any thoughts on how to update to achieve the correct output, I'd be greatly appreciative.
Thanks
Hi @PaulBoden ,
You can create a calculated column as below to get it, please find the details in the attachment.
Output =
VAR _id = 'Table'[ID]
VAR _date = 'Table'[Date]
VAR _case = 'Table'[Case]
VAR _level = 'Table'[Level]
VAR _ladjdate =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
'Table',
'Table'[ID] = _id
&& 'Table'[Flag] = "Adj"
&& 'Table'[Case] = "L"
)
)
VAR _madjdate =
CALCULATE (
MIN ( 'Table'[Date] ),
FILTER (
'Table',
'Table'[ID] = _id
&& 'Table'[Flag] = "Adj"
&& 'Table'[Case] = "M"
)
)
RETURN
IF (
(
'Table'[ID] = _id
&& NOT ( ISBLANK ( _ladjdate ) )
&& 'Table'[Date] >= _ladjdate
&& 'Table'[Case] <> "L"
)
|| (
'Table'[ID] = _id
&& NOT ( ISBLANK ( _madjdate ) )
&& 'Table'[Date] >= _madjdate
&& 'Table'[Case] <> "M"
&& 'Table'[Level] = _level
),
"H"
)
Best Regards
User | Count |
---|---|
20 | |
20 | |
14 | |
10 | |
8 |
User | Count |
---|---|
29 | |
28 | |
13 | |
12 | |
11 |