Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Experts
I have two tables,
Table 1 :Enroll Status with columns
ID | EOI | Enrolled |
Table 2: Payment Portal
ID | Payment |
All I have to do is create visualization and report the status baed on EOI (Expression of Interest, Payment confirmation, Enrolled Status) and give data
1. Do we need to add custom column or can we accomplish the same best way directly in visualizations, I need total EOI, total Enrolled, etc
2. What I did, I added a custom column called it "Status" and in the custom Column wanted to do the following code
= IF(Related(EnrollStaus[EOI]) = "NULL", "Not Interested",
IF(Related(Payment[payment]) <> "NULL", and (Payment[Payment]) <> "NULL" and EnrollStaus[EOI]<> "NULL", "Enrolled" ) etc
It was throwing up error, so debug, just used the following code
IF(Related(EnrollStaus[EOI]) = "NULL", "Not Interested") to try and it has issues with my IF .. please can you explain me why and what is best way to handle
Table 1: Enroll Status
Table 2: Payment Protal
Logic to get Status column
This is the error when I tried just one condition:
Solved! Go to Solution.
Hi @acerNZ ,
Firstly, please change the columns data type from text to date. They will change "NULL" or "Null" to blank values automatically.
In Power Query, you could try the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIL9fGBUbE60UpGQI6xvqGhvpGBkQGQbQpng2SNUUTQZU0Q5oH5pmiq4ZaYATlm2CTMgRxzJAlzFPMtsLjXEtnOWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EOI = _t, Enrolled = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"EOI", type date}, {"Enrolled", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Payment", each (let currentID = [ID] in Table.SelectRows(#"Payment Portal", each [ID] = currentID)){0}[Payment]),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each if [EOI] = null then "Not Interested" else if [Payment] = null then "Yet to Enroll" else "Enrolled")
in
#"Added Custom"
In Report view, please do this:
Column =
SWITCH (
TRUE (),
[EOI] = BLANK (), "Not Interested",
RELATED ( 'Payment Portal'[Payment] ) = BLANK (), "Yet to Enroll",
"Enrolled"
)
For more details, please download the attachment to have a try.
This one worked in the REPORT view, by adding column,
1. Cannot I do this in Query Editor ?
2. Next: I am not able to combine say If (EOI <> "NULL" || "Null" AND if payment portal[payment] <> "NULL" || "Null", "Enrolled") etc?
Hi @acerNZ ,
Firstly, please change the columns data type from text to date. They will change "NULL" or "Null" to blank values automatically.
In Power Query, you could try the M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIL9fGBUbE60UpGQI6xvqGhvpGBkQGQbQpng2SNUUTQZU0Q5oH5pmiq4ZaYATlm2CTMgRxzJAlzFPMtsLjXEtnOWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EOI = _t, Enrolled = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"EOI", type date}, {"Enrolled", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Payment", each (let currentID = [ID] in Table.SelectRows(#"Payment Portal", each [ID] = currentID)){0}[Payment]),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each if [EOI] = null then "Not Interested" else if [Payment] = null then "Yet to Enroll" else "Enrolled")
in
#"Added Custom"
In Report view, please do this:
Column =
SWITCH (
TRUE (),
[EOI] = BLANK (), "Not Interested",
RELATED ( 'Payment Portal'[Payment] ) = BLANK (), "Yet to Enroll",
"Enrolled"
)
For more details, please download the attachment to have a try.
Hi, @acerNZ , of coz you can use such logical expressions in PQ, but with native logical operators of M language, i.e. or/and/not (case-sensitive). Pls refer to official doc for more details.
https://docs.microsoft.com/en-us/powerquery-m/operators
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! |
@CNENFRNL Yes you are right and I got the syntax right
we need to use if, then and else.. all good.
Please can you help me as I was successful in getting data in the coloumns.
Is there a way I extract to data model, only when condition is satified, Example
I am only keen on data which the status is Enrolled, rest of the data should be ignored for processing so that I don't have to keep adding a condition in every statement and filter, also for data security, I don't want other than data where status is "Enrolled"