"Not a fully qualified column reference"
All day I've been trying to figure out why RELATED, RELATEDTABLE, and LOOKUPVALUE don't work for me, and then I realized none of them are working because I'm using Direct Query, but it turns out I'm NOT actually using Direct Query (but should be and need to fix that.) In the meantime, I'm getting the error message that my column is not a "fully qualified column reference"- why else wouldn't my columns be fully qualified if I'm in Import Mode? Meanwhile, I need a new column in the PROPERTY table that lists all the tenants asscociated with each PROP_CODE. The PROP_CODE's match, and you can see it's 1-to-many (which is an issue- I want Power BI to create extra lines in the PROPERTY table to accomodate all the tenants for each PROP_CODE.) RELATED, RELATEDTABLE, and LOOKUPVALUE don't work for some reason. Thanks in advance! Thank-you for your help!
DATEDIFF measure Between two tables that are linked to one table
Hi all, I've come across an issue calculating the datediff between two tables. These two tables aren't connected to each other but rather to another table. To make it easier I'll add a screenshot below. I'd like to calculate the datediff between the quotation closed date in the quotations table and the actual start date in the work orders table. Both quotations and work order is linked to the case table. Would what I want be feasible to do in a measure? Thank you and I appreciate your help as always
Using DAX to sum values from multiple levels of 1-to-many entities in Tabular Model
I have a challenge where I need to create calculated columns, within a tabular model, so our Power BI person can use them, in particular for slicers etc, so I cant really use measures. They need to go on the on the Survey entity (the top most entity on the data structure shown). These calculated columns equate to the quantity of electrical components, and the quantity of fixing components (lets call these two calculated columns cc_Qty_Electrical, and cc_Qty_Fixing). The rules for calculating these are shown later, and the actual way this data is organised isn’t trivial. I have the ability to add calculated columns on the survey entity [which is where I want to put the, and if necessary, I have the power to add calculated columns on the other, lower level entities, but I cant change much else, because the data structure is the back end of a bespoke survey tool, which is provided by an external vendor. The entities have cascading 1:many relationships, as depicted on the diagram. The challenge is that the determination of whether a component is electrical, or fixing, can only be done my looking at combinations of values which belong in all of the cascading entities. The actual combinations are “known” and wont change, so in theory can be hard-coded into the relevant DAX if needed. Rather than explain why, I think it’s best if I just illustrate with some examples of combinations which relate to “Electrical” components (fixing works in the same way, it just has different combinations). (Note *1) For the actual quantity, this is represented in”AD_Answer_Text” but is (frustratingly) stored as text with values which go “01”,”02”,… etc… “10” (i.e. they are text, but they equate to 1,2,…. 10) So, lets take the following data scenario There are several thousand records on the survey entity, …. but for a GIVEN Survey (1332), lets imagine the following set of records. (Note – I have purposely just illustrated the data which relates to “Electrical”. There will be several other sets of records “hanging” off Survey 1332 which aren’t “Electrical”. Conceptually, the DAX functionality needs to detect the various combinations shown, and convert the AD_Answer_Text to an actual integer, and sum up the values. (So in this example, the actual quantity of “electrical components would be (2+5+3+4+1) = 15, which would go into cc_Qty_Electrical on the SURVEY entity. I am pretty sure that the functionality needs to use the “RELATEDTABLE” dax functionality (because this allows a table to be “yielded” from lower level child entities (i.e – it goes from the 1 end to the many end). What I can’t get my head round is, how to chain these together, either in a single large DAX query with multiple RELATEDTABLE calls. I have even tried creating some interim calculated columns at the lower-level entities, but am hitting all sorts of problems. If someone would be kind enough to suggest a strategy, I would be most grateful.
REDO mesures without RELATEDTABLE
Hello, I could not find the answer on youtube, so I ask some help here. I ve got a new dataset to manage which was made not by me. It is very slow and I can't export many visuals to powerpoint directly because it lacks on memory. After investigation - the problem is RELATEDTABLE in DAX syntax - I was able to redo some mesures without relatedtable and it works just fine, but for some mesures I have different result, and old result with RELATEDTABLE is a right one. So my question is there any general tip to get rid of RELATEDTABLE and make my dax more fluent. Example of slow mesure: Base-TotalEng = CALCULATE( [base-F2F]+ [base-Phone]+ [Base-S2S]+ [Base-event]+ [base-FUE]+ [Base-Remote]+ [base-NLADOBE]+ [base-Web] ,RELATEDTABLE('Target') ,RELATEDTABLE('Decision')) Thank you
RANKX, PROBLEM WITH FILTER FROM OTHER TABLE
Im using the following measure: rank= RANKX(ALLSELECTED(T1), sumx(RELATEDTABLE(T1),[K1])) as a dynamic ranking column in a visual table. Wich works, until I filter on another table (T2). Then the filtering gets corrupted. So I get more rows (with empty cells, that shouldnt be there at all), when I add the measure "rank", to the visual. Any solutions?Count % of tickets with only one appointment.
Hi! I need help with how to write the dax code for how to - count number of tickets that only have one appointment in the detail table - count number of ticket that have more than one appointment in the detail table - count number of tickets that do not exist in the detail table I don't get it to work correct. See example in the picture.Adding a new column based on ID with relationship between tables
Hi! I have two tables with a relationship based on order id. What I'm trying to achieve is to add "date" column from "Order Product Histories" to "ORDER PRODUCTS" table. The "date" in "Order Product Histories" table indicates the date of booking and the "date in ORDER PRODUCTS" table indicates the execution date. The case is I have multiple DAX measures already created and I cannot use both relationships between my calendar table and "ORDER PRODUCTS" with "Order Products Histories" at one time. I would like to use the date from "Order Product Histories" table to filter my measures, so I thought it would be the best to add a new column to "ORDER PRODUCTS" table. Looking forward to any tips to make it efficiently work! 🙂
