Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I need to move this calculated column upstream in the process to Power Query. It should be simple enough, but I am struggling. The basic idea behind the calculated column is to evaluate whether a type of service occurred during an authorization span of that type of service. The DAX works, but I need it in M.
Optum Approved =
VAR OptumTable =
FILTER(
'FACT-Optum Auth Status',
'FACT-Service'[Optum ID] = 'FACT-Optum Auth Status'[Optum ID]
&& 'FACT-Service'[Contact Date] >= 'FACT-Optum Auth Status'[Optum Auth Start Date]
&& 'FACT-Service'[Contact Date] <= 'FACT-Optum Auth Status'[Optum Auth End Date]
&& 'FACT-Optum Auth Status'[Procedure Type] = 'FACT-Service'[Service]
)
RETURN
IF( COUNTROWS( OptumTable ) >=1,1,0)
Solved! Go to Solution.
Hi @jguercio,
There are a number of ways to deal with this.
Would have been easier to answer definitively with some sample data but check out the below code and attached PBIX for an example.
Basically, I merged based on id and service and then added the conditional column when between the two dates.
authStatus
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMlTSUTLUN9Q3MjAyAjITlWJ1opVALCMDfSOYaBJY1Bik1ELfGCaaDBY1gaiFm5CiFBsLAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [id = _t, contactDate = _t, service = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"id", Int64.Type}, {"contactDate", type date}, {"service", type text}}
),
#"Merged Queries" = Table.NestedJoin(
#"Changed Type",
{"id", "service"},
authStatus,
{"id", "procedureType"},
"authStatus",
JoinKind.LeftOuter
),
#"Expanded authStatus" = Table.ExpandTableColumn(
#"Merged Queries",
"authStatus",
{"authSD", "authED"},
{"authSD", "authED"}
),
#"Added Custom" = Table.AddColumn(
#"Expanded authStatus",
"Custom",
each try if [contactDate] >= [authSD] and [contactDate] <= [authED] then 1 else 0 otherwise 0
)
in
#"Added Custom"
service
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMlTSUTLUN9Q3MjAyAjITlWJ1opVALCMDfSOYaBJY1Bik1ELfGCaaDBY1gaiFm5CiFBsLAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [id = _t, contactDate = _t, service = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"id", Int64.Type}, {"contactDate", type date}, {"service", type text}}
),
#"Merged Queries" = Table.NestedJoin(
#"Changed Type",
{"id", "service"},
authStatus,
{"id", "procedureType"},
"authStatus",
JoinKind.LeftOuter
),
#"Expanded authStatus" = Table.ExpandTableColumn(
#"Merged Queries",
"authStatus",
{"authSD", "authED"},
{"authSD", "authED"}
),
#"Added Custom" = Table.AddColumn(
#"Expanded authStatus",
"Custom",
each try if [contactDate] >= [authSD] and [contactDate] <= [authED] then 1 else 0 otherwise 0
)
in
#"Added Custom"
I hope this helps. If not, please provide some sample data.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Hi @jguercio,
There are a number of ways to deal with this.
Would have been easier to answer definitively with some sample data but check out the below code and attached PBIX for an example.
Basically, I merged based on id and service and then added the conditional column when between the two dates.
authStatus
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMlTSUTLUN9Q3MjAyAjITlWJ1opVALCMDfSOYaBJY1Bik1ELfGCaaDBY1gaiFm5CiFBsLAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [id = _t, contactDate = _t, service = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"id", Int64.Type}, {"contactDate", type date}, {"service", type text}}
),
#"Merged Queries" = Table.NestedJoin(
#"Changed Type",
{"id", "service"},
authStatus,
{"id", "procedureType"},
"authStatus",
JoinKind.LeftOuter
),
#"Expanded authStatus" = Table.ExpandTableColumn(
#"Merged Queries",
"authStatus",
{"authSD", "authED"},
{"authSD", "authED"}
),
#"Added Custom" = Table.AddColumn(
#"Expanded authStatus",
"Custom",
each try if [contactDate] >= [authSD] and [contactDate] <= [authED] then 1 else 0 otherwise 0
)
in
#"Added Custom"
service
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMlTSUTLUN9Q3MjAyAjITlWJ1opVALCMDfSOYaBJY1Bik1ELfGCaaDBY1gaiFm5CiFBsLAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [id = _t, contactDate = _t, service = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"id", Int64.Type}, {"contactDate", type date}, {"service", type text}}
),
#"Merged Queries" = Table.NestedJoin(
#"Changed Type",
{"id", "service"},
authStatus,
{"id", "procedureType"},
"authStatus",
JoinKind.LeftOuter
),
#"Expanded authStatus" = Table.ExpandTableColumn(
#"Merged Queries",
"authStatus",
{"authSD", "authED"},
{"authSD", "authED"}
),
#"Added Custom" = Table.AddColumn(
#"Expanded authStatus",
"Custom",
each try if [contactDate] >= [authSD] and [contactDate] <= [authED] then 1 else 0 otherwise 0
)
in
#"Added Custom"
I hope this helps. If not, please provide some sample data.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.