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!
Formatting Data from three tables into a dynamic report
Hi, I have three data tables I'm trying to combine into dynamic reports. They are contain unique ID keys so the relationships are in place, but I'm having trouble dispaying them in a way that is flexible enough and not incredibly complex to set up. Table 1 contains projects with various fields Table 2 contains the leads for the projects, with various fields including the project ID corresponding to table 1. Each project will have exactly 1 lead and each lead will only have 1 project. Table 3 contains other team members with various fields. There will be at least one in addition to the lead for every project, but often there will be more than one, up to around twenty. This table contains the ID for the project and lead each team member works with, and no team member will ever have more than one project/lead. I'd like to combine these into a report in a format like: Header with all project fields Project 1 Header with all lead fields Lead for project 1 Header with all team member fields Team member 1 of XX for project 1 Team member 2 of XX for project 1 .... Then the same for project 2 and so on. I can see how I would do this with code, or with a really complicated spreadsheet with lots of counts, lookups and helper collumns, but I'm hoping Power BI can help me find a simple-ish solution. Thanks for any advice!
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 appreciatedLooking 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!
