Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Kifl2023
Regular Visitor

Lookup text value with multiple conditions in another query

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. 

Kifl2023_1-1682029465448.png

 

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_0-1682029380216.png

 

3 REPLIES 3
Kifl2023
Regular Visitor

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 😊

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
Community Support
Community Support

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:

vyueyunzhmsft_0-1682304122791.png

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 :

vyueyunzhmsft_1-1682304276660.png

 

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:

vyueyunzhmsft_2-1682304483869.png

 

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:

vyueyunzhmsft_4-1682304628826.png

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

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.