Forum Discussion
go into "Table" create if Condition, formula help
hello, here is a screenshot. basically i merged 2 tables based on a matching ID, so for each ID it creates a table of all matching entries... what I want to do is create a formula for each new Column that looks at the 'Date' column of embedded Table and test if certain conditions are met. The column headers has the condition.... there is a "Blood Date" in main table i need to refrence as well
*see screenshot, ("Date" referes to Date in embedded Table)
column3, Called NovDec = if Date is < Jan 1 2023 then 1 else 0
column4, Called Jan = if Date is > Jan 1 2023 AND before Jan 31,2-23 then 1 else 0
column5, Called Feb before Blood Date = if Month(Date )='Feburary" AND Date <= "Blood Date" then 1 else 0
column6, Called Feb after Blood Date = if Date > "Blood Date" then 1 else 0
im just not sure the syntax to refer to embedded table. tahnks for any help with these formulas
Hi ilcaa722 ,
Please create 4 custom columns.
Called NovDec:
if List.IsEmpty(List.Select([MD_call_Data][Date],each _ < #date(2023,1,1))) then 0 else 1//Called Jan = if Date is > Jan 1 2023 AND before Jan 31,2-23 then 1 else 0
There seems to be a slight typo here, and I'm guessing you want this.
Called Jan:
if List.IsEmpty(List.Select([MD_call_Data][Date],each _ >= #date(2023,1,1) and _ <= #date(2023,1,31) )) then 0 else 1Called Feb before Blood Date:
if List.IsEmpty(List.Select([MD_call_Data][Date],(x)=> Date.MonthName(x) = "Feburary" and x <= [Blood Date])) then 0 else 1Called Feb after Blood Date:
if List.IsEmpty(List.Select([MD_call_Data][Date],(x)=> x > [Blood Date])) then 0 else 1Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
1 Reply
Hi ilcaa722 ,
Please create 4 custom columns.
Called NovDec:
if List.IsEmpty(List.Select([MD_call_Data][Date],each _ < #date(2023,1,1))) then 0 else 1//Called Jan = if Date is > Jan 1 2023 AND before Jan 31,2-23 then 1 else 0
There seems to be a slight typo here, and I'm guessing you want this.
Called Jan:
if List.IsEmpty(List.Select([MD_call_Data][Date],each _ >= #date(2023,1,1) and _ <= #date(2023,1,31) )) then 0 else 1Called Feb before Blood Date:
if List.IsEmpty(List.Select([MD_call_Data][Date],(x)=> Date.MonthName(x) = "Feburary" and x <= [Blood Date])) then 0 else 1Called Feb after Blood Date:
if List.IsEmpty(List.Select([MD_call_Data][Date],(x)=> x > [Blood Date])) then 0 else 1Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum