Conditional Code Branching in Power BI Query: if...then...else => then...else...if

[C097986]

Hello All,

       I was trying to write a code where i can use If and then clauses but i was haivng trouble b/c there are multiple clauses that i want to add. i figured out if it's only one but cannot with mulitple clauses.

For example: i wanted to get a column= status that equals only "draft", then give me a result of a submission date that is minus 7 days.

i want to see a date shown that comes with filters that shows status only in draft and submission date that is x - 7 (07/09/18 minus 7 days)

Submission Email = IF((AEF_APPROVAL_DETAILS[STATUS]="Draft")then AEF_REQUEST_FORM_DATA[Submitted].[Date]-7)

But this doesn't work at all. any help would be appreciated in me trying to find a solution.

thank you

[v-yuta-msft]

Hi C097986,

Are 'AEF_APPROVAL_DETAILS' and 'AEF_REQUEST_FORM_DATA' two different tables? The if clause should be implement in one table, so I would recommend you to merge table AEF_APPROVAL_DETAILS and AEF_REQUEST_FORM_DATA firstly and then create a custom on the new table using M code like your pattern. In addtion, if you can't merge them, you can create a calculate column using DAX formula like below(should have one-to-one relationship between the two tables):

New Column =
IF (
    AEF_APPROVAL_DETAILS[STATUS] = "Draft",
    RELATED ( AEF_REQUEST_FORM_DATA[Submitted].[Date] ) - 7
)

Regards,

Jimmy Tao

[C097986]

yes it is two table, but i tried this and it still does not work.

give me this error: The column 'AEF_REQUEST_FORM_DATA[Submitted].[Date]' either doesn't exist or doesn't have a relationship to any table available in the current context.

the tables are mapped correctly and have a relationship, and the column exists b/c i use that for other formulas. so i'm not sure why this error shows up?

[v-yuta-msft]

Hi C097986,

For further analysis, could you share some tables with dummy data to clarify your issue?

Regards,

Jimmy Tao