How to combine Lookupvalue, If and AND statement.

[Anonymous]

Hello,

I have a problem that I tried to look for in this discussion as well, but I couldn't find anything that could help me.

I would like to create a function that would compare the columns
Uncleansed sales history and Statistical FC LC Lag 1 for the last 6 months.

if the condition is true for all 6 rows
Row(index) 0 Uncleansed sales history > Statistical FC LC Lag 1
Row(index) 1 Uncleaned sales history > Statistical FC LC Lag 1
Row(index) 2 Uncleansed sales history > Statistical FC LC Lag 1
Row(index) 3 Uncleansed sales history > Statistical FC LC Lag 1
Row(index) 4 Uncleansed sales history > Statistical FC LC Lag 1
Row(index) 5 Uncleansed sales history > Statistical FC LC Lag 1

return "Overforecast last 6 months"

if this condition does not apply to at least one, return me "blank"

thank you for every answer

[v-yadongf-msft · Accepted solution]

Hi @Anonymous ,

Your current DAX formula doesn't affect your memory and Ithink you've solved your problem.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yadongf-msft]

Hi @Anonymous ,

Please try following DAX:

Column = SWITCH(
    TRUE(),
    'Table'[Index] = 0 &&'Table'[Index] = 1 &&'Table'[Index] = 2 
    && 'Table'[Index] = 3 &&'Table'[Index] = 4 && 'Table'[Index] = 5 
    && 'Table'[Uncleansed sales history] > 'Table'[Statistical FC LC Lag 1],
    "Overforecast last 6 months",
    BLANK())

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi About v-yadongf-msft,

thank you for your answer, but it is not working. I finally managed to do it, but I had to create too many auxiliary calculated columns where. I'll send you a photos of my process step by step. 

As I already wrote above
I would like to create a function that would compare the columns
Uncleansed sales history and Statistical FC LC Lag 1 for the last 6 months.

f the condition is true for all 6 rows
Row(index) 0 Uncleansed sales history > Statistical FC LC Lag 1
Row(index) 1 Uncleaned sales history > Statistical FC LC Lag 1
Row(index) 2 Uncleansed sales history > Statistical FC LC Lag 1
Row(index) 3 Uncleansed sales history > Statistical FC LC Lag 1
Row(index) 4 Uncleansed sales history > Statistical FC LC Lag 1
Row(index) 5 Uncleansed sales history > Statistical FC LC Lag 1

return "Overforecast last 6 months"

if this condition does not apply to at least one, return me "blank"

so what i did

1. Step)

2.Step

3. Step

Is there any way to make it easier and save memory?

Thank you

[lbendlin]

Please provide sanitized sample data that fully covers your issue. https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523