Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need to create a calculated column that looks at ID, Type and Date in the table below, then:
IF [type] = "121 meetings",
[Date] switch earliest for "1st 121 meeting", the second earliest date is switched to "2nd 121 meeting" and additional dates are "additional 121 meetings".
ID | Type | Date | New column | |
1 | 121 meeting | 01/02/2021 | 1st 121 meeting | |
1 | 121 meeting | 03/02/2021 | 2nd 121 meeting | |
2 | 121 meeting | 07/02/2021 | 2nd 121 meeting | |
2 | 121 meeting | 02/02/2021 | 1st 121 meeting | |
3 | 121 meeting | 09/02/2021 | additional 121 meeting |
Hope this makes sense,
thanks in advance.
Solved! Go to Solution.
If I understand your requirements correctly, the new column is based on the occurrence of "121 meeting" per ID.
If creating a DAX calculated column, something like this should work (returning blank if Data[Type] <> "121 meeting"):
New column DAX =
IF (
Data[Type] = "121 meeting",
VAR MeetingDates =
CALCULATETABLE (
VALUES ( Data[Date] ),
ALLEXCEPT ( Data, Data[ID], Data[Type] ) -- Keep filters on ID and Type (must be "121 meeting")
)
VAR Occurrence =
RANKX ( MeetingDates, 'Data'[Date], , ASC )
VAR Result =
SWITCH (
Occurrence,
1, "1st 121 meeting",
2, "2nd 121 meeting",
"additional 121 meeting"
)
RETURN
Result
)
By the way, for ID=3 should the column be "1st 121 meeting".
Regards,
Owen
Hi, @Anonymous , you might want to try
Solution CC =
VAR __topn =
TOPN (
2,
FILTER (
Schedule,
Schedule[ID] = EARLIER ( Schedule[ID] ) && Schedule[Type] = EARLIER ( Schedule[Type] )
),
Schedule[Date], ASC
)
VAR __1st = MINX ( __topn, Schedule[Date] )
VAR __2nd = MAXX ( __topn, Schedule[Date] )
RETURN
SWITCH (
TRUE (),
Schedule[Date] = __1st, "1ST",
Schedule[Date] = __2nd, "2ND",
"Additional"
)
Personllay, I prefer to accomplish it in PQ; here's the snippet of M code for your reference,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MlTITU0tycxLB/IMDPUNjPSNDIwMlWJ1sCowRlFghKnAnJACIxQFxpgKLDHcYGlpid+RaAqMCCnAdCReE7AoMCGgwJCQCYZIJsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Date", type date}}, "Fr"),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Solution PQ",
each
let
dates = Table.Group(#"Changed Type", {"ID", "Type"}, {{"ar", each _}}){[ID=[ID], Type=[Type]]}[ar][Date],
earliest = List.Min(dates),
#"2nd ealiest" = List.MinN(dates, 2){1}?
in
if [Date]=earliest then "1st" else if [Date]=#"2nd ealiest" then "2nd" else "additional"
)
in
#"Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi, @Anonymous , you might want to try
Solution CC =
VAR __topn =
TOPN (
2,
FILTER (
Schedule,
Schedule[ID] = EARLIER ( Schedule[ID] ) && Schedule[Type] = EARLIER ( Schedule[Type] )
),
Schedule[Date], ASC
)
VAR __1st = MINX ( __topn, Schedule[Date] )
VAR __2nd = MAXX ( __topn, Schedule[Date] )
RETURN
SWITCH (
TRUE (),
Schedule[Date] = __1st, "1ST",
Schedule[Date] = __2nd, "2ND",
"Additional"
)
Personllay, I prefer to accomplish it in PQ; here's the snippet of M code for your reference,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MlTITU0tycxLB/IMDPUNjPSNDIwMlWJ1sCowRlFghKnAnJACIxQFxpgKLDHcYGlpid+RaAqMCCnAdCReE7AoMCGgwJCQCYZIJsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}, {"Date", type date}}, "Fr"),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Solution PQ",
each
let
dates = Table.Group(#"Changed Type", {"ID", "Type"}, {{"ar", each _}}){[ID=[ID], Type=[Type]]}[ar][Date],
earliest = List.Min(dates),
#"2nd ealiest" = List.MinN(dates, 2){1}?
in
if [Date]=earliest then "1st" else if [Date]=#"2nd ealiest" then "2nd" else "additional"
)
in
#"Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
If I understand your requirements correctly, the new column is based on the occurrence of "121 meeting" per ID.
If creating a DAX calculated column, something like this should work (returning blank if Data[Type] <> "121 meeting"):
New column DAX =
IF (
Data[Type] = "121 meeting",
VAR MeetingDates =
CALCULATETABLE (
VALUES ( Data[Date] ),
ALLEXCEPT ( Data, Data[ID], Data[Type] ) -- Keep filters on ID and Type (must be "121 meeting")
)
VAR Occurrence =
RANKX ( MeetingDates, 'Data'[Date], , ASC )
VAR Result =
SWITCH (
Occurrence,
1, "1st 121 meeting",
2, "2nd 121 meeting",
"additional 121 meeting"
)
RETURN
Result
)
By the way, for ID=3 should the column be "1st 121 meeting".
Regards,
Owen
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 |
---|---|
54 | |
26 | |
20 | |
15 | |
11 |
User | Count |
---|---|
77 | |
62 | |
44 | |
18 | |
12 |