go into "Table" create if Condition, formula help

[ilcaa722]

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

[v-cgao-msft · Accepted solution]

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 1

Called 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 1

 Called Feb after Blood Date:

if List.IsEmpty(List.Select([MD_call_Data][Date],(x)=> x > [Blood Date])) then 0 else 1

Best Regards,
Gao
Community Support Team

If 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