Look at 6+ Columns, Return Related Value
Hi! I am trying to figure out a formula that looks at 6+ values in a row, references my related table and returns the matching value. The LOOKUPVALUE + RELATED function combined has gotten me results for a particular value, however I need to analyze multiple columns. Any help would be greatly appreciated! Thank you! M
Lookup between virtual tables without relationship
Hi guys, I am quite new to DAX magic and been practicing a lot in the last few months, but still I can't fully understand the concept of lookup values based on columns from "virtual tables", even though I've read the articles about it, and also about the TREATAS function that probably should help here. No matter which method i've used in my code to add the column of lookup value from one table to another, I'm getting the same message of "table variable "xxx" cannot be used in this concept, because a base table is expected" 😕 Anyway, my example is very simple: DEFINE VAR _size = ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'person'[person id] ), "total", 'measures'[total] ) VAR _tbl = ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'person'[person id], 'mgr'[mgr id] ), "lookup", LOOKUPVALUE ( '_size'[total], '_size'[person id], '_tbl'[mgr id], 0 ) ) EVALUATE _tbl definitions: 'person' and 'mgr' are actual physical tables, [total] is a meassure as part of the model. so the idea is to add a new column to _tbl1 called "lookup" that will bring the [total] for each [mgr id] matching [mgr id] to [person id]. so as mentioned the error i get is about the fact that those tables are not physical ones in my model, but a virtual temp tables that i'm creating in thie query. so what's the method that can be used to do a lookup between non-related virtual table variables? thanks!
RELATED function for Lookup not working
Hi there, I am having difficulties with the RELATED formula and hoping someone can help? All I am trying to recreate an Excel style Vlookup table. Am I going about this wrong way? I have two Queries, TEST1 & LOOKUP. I am trying to add a new column in TEST1 that will return the value from the ‘Area’ column in the LOOKUP query column, where ‘Type (name)’ (from TEST1) matches ‘Type (name)’ (from LOOKUP). This is the code so far that I am placing inside a Custom Column [#"Type (name)"] = RELATED(LOOKUP[Area]) I have created a one to many relationship for the Type (name) in TEST1 & LOOKUP But this is the error message. Does anyone have any idea where I am going wrong? Any help massively appreciated
Looking for specific text in cell based on another column
I'm trying to do a Find/Mid/Match in Power Query, but I'm not sure how to do it. Basically, I have a long list of article numbers and article names - inside these articles names it's stated which colour the product is. The issue is that it's not always in the same place. I have a query named "X_Colours" with one column "Colours" type is Text. Other query is "Items" with two columns "Item No" (Number) and "Name" (Text). I would like a custom column that looks in "Items" "Name" for a similar value from "X_Colours" "Colours" column. If it finds the same value, it should return the value from "X_Colours" if not, it should just return something like "Unknown", so I can manually look for the colour and add it to the list. Is this possible or do I have to create a new table and use an excel formula and then add it back into the query? Thanks in advance!
If a related table column value = x, then return another related table column value
This is a very simple thing that I am trying to do. I have two tables that have a relationship between each other. I just want to say, if tblPricing has "Individual" in the [Pricing Structure] column, then return the adjacent [Price] values from the same table. However, I am getting this silly error about expressions and variables. Can someone please tell me why this error is happening and how I should do a simple lookup like this?
Returning value from another table (multiple value but setting a preference what to return)
i have a scenario where i need to return a state of a something from a state tracker (i.e. table 1 below to table 2) as it will return multiple value for certain items, how could the Dax return value based on below scenario ? (i. if the item has Active state at all, always return Active & if there is no Active state but other multiple state , return either 1 -maybe first hit of state alphabetical order) been googling around, the nearest is to use FIRSTNONBLANK, but i cant assure Active state is always the first hit as in the case of SVR1 below
