DAX measure Lookup in IF condition

[Dora_Sun]

Hi, I haven't been able to find a solution to my problem for several days now, so hoping someone could help here.

I would like to have an additonal column (Headline Description) in my table (CJ33_Summary) that has values from another column (Headlines Description) from a different table (Project_Lables) depending on the values in columns 'CJ33Summary'[Account assign elem] and 'CJ33Summary'[SAP_Code].

I have created the below logic in excel which works well in excel, so I was hoping I can just translate my excel formula into DAX, however it doesn't work in my measure. I have played around with a few options but none of them work:

Option 1:

IF('CJ33_Summary'[Account assign elem]="J570.PL.GAC.ENABLING", LOOKUPVALUE('CJ33_Summary'[Account assgn elem],Project_Lables[Project code],[Headlines Description]),
LOOKUPVALUE('CJ33_Summary'[SAP_Code],Project_Lables[Project code],[Headlines Description]))

Option 2:

IF(OR('CJ33_Summary'[Account assign elem]="J570.PL.GAC.ENABLING", ('CJ33_Summary'[Account assgn elem]="J570.PL.GAC.ENABLING")),
IFERROR(LOOKUPVALUE('CJ33_Summary'[Account assgn elem],Project_Lables[Project code],[Headlines Description],
(LOOKUPVALUE('CJ33_Summary'[SAP_Code],Project_Lables[Project code],[Headlines Description]))

Option 3:

Headline Description = IF(SUM(CJ33_Summary[J570.PL.GAC.ENABLING]),LOOKUPVALUE('CJ33_Summary'[Account assgn elem],Project_Lables[Project code],[Headlines Description]),LOOKUPVALUE('CJ33_Summary'[SAP_Code],Project_Lables[Project code],[Headlines Description]))

In Option 3 I created a numeric column that has a value 1 if CJ33_Summary'[Account assign elem]="J570.PL.GAC.ENABLING". As I found in some sources that DAX has issues recognising columns when the value column is not aggrigated. 

All of the options above are having issues recognising the last element of the lookup function [Headlines Description]. 

Option 1 and 2 doesn't recognise any of the columns. 

Please see attached error of option 1 and 3.

Thanks,

Dora

[Dora_Sun]

Hi, I still haven't found a received a solution to this issue. I managed to set up a Lookup formula within an if condition and iferror which are working, but only for numeric values. I still don't have a solution for looking up text columns. Could you please advise if this is currently not possible in DAX and so I would have to set up an external excel file to run such processing? 

Thank you,

Dora

[Fowmy]

@Dora_Sun 

Can you try this measure?

Measure = 
VAR ACC =  SELECTEDVALUE('CJ33_Summary'[Account assign elem]) 
VAR SAP =  SELECTEDVALUE('CJ33_Summary'[SAP_Code]) 
RETURN
IF(
    ACC = "J570.PL.GAC.ENABLING", 
    LOOKUPVALUE(ACC,Project_Lables[Project code],[Headlines Description]),
    LOOKUPVALUE(SAP,Project_Lables[Project code],[Headlines Description])
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Dora_Sun]

also it's giving this error message: "Function LOOKUPVALUE expects a column reference as argument number 1."

Thanks,

Dora

[Dora_Sun]

Hi, thank you for your quick reply. Unfortunately this has the same issue as the other examples I had, the column names are not recognised in the measure. 

[AlB]

Hi @Dora_Sun, 

Your explanation is not clear. Always show sample data and an example based on that data with the expected result so that the requirement can be easily understood.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Dora_Sun]

Hi, thank you for your quick reply. I can't give you sample data as none of my options are working so I have no data of the result. it is working in excel, so dos it help if I give you the excel formula that' working?: 

=IF([@[Account assgn elem]]="J570.PL.GAC.ENABLING",VLOOKUP([@[Account assgn elem]],Project_Lables[[Project code]:[Headlines Description]],8,FALSE),IFERROR(VLOOKUP([@[SAP_Code]],Project_Lables[[Project code]:[Headlines Description]],8,FALSE),"Reactive Risks"))

they are all text type columns.

basically if it's not clear, what the above does, is if 'CJ33_Summary'[Account assgn elem]]="J570.PL.GAC.ENABLING" then use the Project_Lables[[Project code] to lookup the Headlines Description values, otherwise, use the 'Project_Lables'[SAP_Code] to lookup the Headlines Description values, and otherwise write "Reactive Risks". 

Does this make more sense?

Thanks.