RELATED does not work despite one to one relationship?
I am trying to implement a filter from two tables with AND condition to Invoiced Amount: Retail Invoiced Amount = CALCULATE( [Invoiced Amount EUR], 'Legal Unit Filter'[Procurement KPI]="Retail KPI", FILTER( 'Business Unit', RELATED('Business Unit Filter'[Procurement G&SF.H&T KPI ])<>"Yes" ) ) But Power BI gives me an error: The column 'Business Unit Filter[Procurement G&SF.H&T KPI ]' either doesn't exist or doesn't have a relationship to any table available in the current context. This is strange since Business Unit and Business Unit Filter tables have a One-to-one relationship: Business Unit then has a One-to-Many relationship with the Invoice table. What can be the problem here? When I try the same setup in a test file with same set of table relationships there is no error and Invoice Amount is calculated correct. I am working in a composit model could that effect how RELATED works?
Related in composite model
Greetings, I have a two tables, one that is imported(Products), the other is DirectQuery(Lignes_A). There is a many to one relationship (Lignes_A being many and Products being one). I'm trying to use the related function to get the value "family" that is in Products. Related even recognize the products table as a related table, but once I valid the Dax, it says it isn't possible because these tables arent related. From what I could gather, this is basically an issue that's been going around for a year now. How can I go around this issue? Here's the Dax query I've come up with (which was working while I still had everything on import, but due to size is not a viable option): SpentDuringRebate = SUMX(FILTER(LIGNES_A, RELATED(PIEDS_A[ID_FOU]) = 'reporting SupplierRebatesRules'[SupplierId] && ('reporting SupplierRebatesRules'[ExcludedFamille] = BLANK() || 'reporting SupplierRebatesRules'[ExcludedFamille] <> RELATED(Products[FAMILY])) && ('reporting SupplierRebatesRules'[ExcludedRayon] = BLANK() || 'reporting SupplierRebatesRules'[ExcludedRayon] = RELATED(Products[RAYON])) && ('reporting SupplierRebatesRules'[RestrictedFamille] = BLANK() || 'reporting SupplierRebatesRules'[ExcludedRayon] <> RELATED(Products[FAMILY])) && ('reporting SupplierRebatesRules'[RestrictedRayon] = BLANK() || 'reporting SupplierRebatesRules'[RestrictedRayon] = RELATED(Products[RAYON])) && RELATED(PIEDS_A[DATE]) >= 'reporting SupplierRebatesRules'[DateStart] && RELATED(PIEDS_A[DATE]) < 'reporting SupplierRebatesRules'[DateEnd]), [TOT_NET_HT]) And here's an image of my relationships. I highlighted the foreign keys. Does anyone know what I can use instead of Related, as it is not working?Composite Model and Direct query, loop up values from 2 tables
Question regarding Composite models I have a Direct Query Table containg tons of data - DqTable(codes, date, name, status) I have an imported table containing all the rules - RulesTable(name,status, newstatus) I want to count number of codes which satisfy all the rules Ex. DqTable: codes date name status 45852 1/2/19 ABC Completed 78513 1/3/19 DCV WIP-1 65753 1/3/19 ABC Not Completed RulesTable: name status newstatus ABC Completed C1 ABC WIP C2 ABC Not Completed C3 DCV WIP-1 C2 [name] is common and I can make a relationship. I tried to make a calculated table and failed. I tried lookup function it works good as a measure but I used SELECTEDVALUE TO Select each code - but i am not able to save the results. So i want to do it in calculated table, i want the below result date name C1 C2 C3 1/2/19 ABC 1 - - I tried this code: CalcTable: ADDCOLOUMNS(SUMMURIZE(DqTable, DqTable[date], DqTable[names]), "C1", Calculate(COUNT(DqTable[codes]), LOOKUPVALUE(RulesTable[newstatus],RulesTable[status],DqTable[status], RulesTable[name],DqTable[name]) = "C1") But i got LOOKUPVALUE function cannot be used as a Table Filter, I tried passing LOOKUPVALUE as a var, but it is not detecting DqTable[status] Its complex please try to help me and let me know if you want any details
