Lookup text value with multiple conditions in another query

[Kifl2023]

I have a query setup for operating expense with a store number and then I have a key table with ownership status and usage type. store numbers are repeating. Example, store 68, has leased and owned status but the usage type for owned is store and the leased usage type is pylon. 

I'm using lookupvalue dax but I'm getting this error.  Can anyone please help me with dax formula to add a column in operating expense query in which I can get the location type result using a filter of usage of retail.

[Kifl2023]

Hello Aniya! 

I extremely appreciate your help and I tried the code it works. However, I think I wasn't able to address my question clearly. My goal was to create two columns one called retail, and one called non-retail, which will include the status of location type for (Pylon and other categories). Is there a code that I can use which can give me a status of the location type for a store if I choose the condition of the usage as pylon, then instead of giving me an answer of lease/owned - it should just say its leased and when I ask for the retail store, it gives me a status of owned in seperate column. Please advise. Thanks 😊

[v-yueyunzh-msft]

Hi, @Kifl2023 

According to your description, you want to " give me a status of the location type for a store if I choose the condition of the usage as pylon" and you want to add two columns.

If the "if I choose the condition of the usage as pylon" means that you use a filter , then this is not possible in Power BI because the computed column cannot get the value selected by the slicer.

If I misunderstood you,can you provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )Simple data can make the problem more intuitive~

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[v-yueyunzh-msft]

Hi , @Kifl2023 

According to your description, you want to " add a column in operating expense query in which I can get the location type result using a filter of usage of retail.".

The first thing to be clear is that you want to return different values in the column depending on FIlter's selection. This is currently not possible because the Calculated Column cannot be affected by the slicer and return a different value, and if you want to use the filter, then you can only create meausre.

This is my test data:

If you have relationship between two tables and you select one value for the [Usage] fields , you can just put the fields on the visual and we can get the result :

If you do not have relationship between two tables, we can create a measure like this:

Measure = var _t =DISTINCT( SELECTCOLUMNS( FILTER('Key' ,'Key'[Store Number] = MAX('Operating Expense'[Store Number])) , "type" , [Location Type]))
return
CONCATENATEX(_t ,[type] ,",")

The result is as follows:

If you still need to add a column in your table , first the filter will not work and you can not use the LLOKUPVALUE() function,you can create a calculated column :

Column = var _t =DISTINCT( SELECTCOLUMNS( FILTER('Key' , 'Key'[Store Number] =EARLIER('Operating Expense'[Store Number]))  ,"type" , [Location Type]))
return
CONCATENATEX(_t , [type] , ",")

The result is as follows:

You can also modify the dax code after return to meet your need.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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