One table Vlookup equivalent in DAX
Dear community, I'm trying to get shipment C013720578 to be shown in column Group for all rows. Shipment C013720578 is the main shipment, out of 4 shipments linked to invoice 1891846520. I need to do this for all Invoices in column Invoice. My first try was lookupvalue Column = LOOKUPVALUE('BRIDGE FULL'[Shipment],'BRIDGE FULL'[Invoice & main],'BRIDGE FULL'[Invoice & Shipment Type]) This wouldn't work becuase there are duplicates (in the Search Value I think), and I got this error: A table of multiple values was supplied where a single value was expected. How do I modify the formula below please? or something completely different? (hint: shipment C012293295 currently shown in column Group is the very first shipment of the entire big table) Invoice Shipment Shipment Type Shipment Type - Main Invoice & main Invoice & Shipment Type Group 1891846520 C013720583 ZT13 not main 1891846520main 1891846520not main C012293295 1891846520 C013720577 ZT11 not main 1891846520main 1891846520not main C012293295 1891846520 C013720579 ZT13 not main 1891846520main 1891846520not main C012293295 1891846520 C013720578 ZT12 main 1891846520main 1891846520main C012293295 1891846523 C013720577 ZT11 not main 1891846523main 1891846523not main C012293295 1891846523 C013720579 ZT13 not main 1891846523main 1891846523not main C012293295 1891846523 C013720586 ZT13 not main 1891846523main 1891846523not main C012293295 1891846523 C013720578 ZT12 main 1891846523main 1891846523main C012293295 1891846524 C013720579 ZT13 not main 1891846524main 1891846524not main C012293295 1891846524 C013720586 ZT13 not main 1891846524main 1891846524not main C012293295 1891846524 C013720577 ZT11 not main 1891846524main 1891846524not main C012293295 1891846524 C013720578 ZT12 main 1891846524main 1891846524main C012293295
"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!
Lookupvalue results missing
Hi all, I have tried to create a Lookup between two tables. What I am trying to do is to determine what is the max amount of days that a project can take based on the SLA table. This SLA has a variety of critera: Wordcount, service needed, department and type of material. I have odd results and only one of the max SLA get populated, the rest they do not seem to be matched, anyone would know why?
DAX lookupvalue with date condition
Hello, I want to lookupvalue based on a particular date. There are 2 tables. Table 1 with meter_id, power outage column and measurement date. Table 2 with child meters associated with the meter_id from table 1. Now I want to lookupvalue in table 2 based on following conditions: 1. Meter_id with power outage flag set to 1 in table 1 2. Lookup values only for meter_ids with the selected measurement date. The measurement date is linked to a calendar table and the calendar date from calendar table is used to select the measurement date. Finally in table 2 I want to set a power outage flag for those child meters associated with the meter_id from table 1 For example: If the selected date : 10.5.2023, then only the 100A meter with power outage flag set to 1 should be used to lookup value in table 2 and its corresponding child ABC1 should have the child power outage set to 1. The other child meters shouldn't be set to 1 because the power outage for its main meter was not on 10.5.2023 Table 1 Meter ID date power outage 100A 10.5.2023 1 1000B 11.6.2023 1 123C 22.11.2022 145D 13.5.2023 1 Table 2 Meter ID Parent ID child power outage ABC1 100A 1 X1008 1000B XV200 145D XY100 146E Currently, I wrote a DAX function in table 2 as follows: child_power_outage = LOOKUPVALUE(table 1[power_outage],table 1[Meter ID],[Parent ID],table 1[power_outage],1) But this gives: Table 2 Meter ID Parent ID child power outage ABC1 100A 1 X1008 1000B 1 XV200 145D 1 XY100 146E Is there a way to include a date condition or date filter in lookupvalue. ? I have tried merging table 1 and table 2, but power query is not allowing it because table 1 has other queries merged with it and it says it references other queries, so it may not directly access a data source, so rebuild this data combination. Any little bit of help is much appreciated. I am stuck at this problem for a long time. Thanks a lot.
Dudas con Lookupvalue
Hola a tod@s, LOOKUPVALUE ( 'Foto BI Inicial Detallada'[Número documento], 'Foto BI Inicial Detallada'[Número documento], 'Foto BI Final Detallada'[Número documento], 'Foto BI Inicial Detallada'[Fecha Foto],Date(2023,9,4),"No" ) Como podría sustituir Date(2023,9,4) por el valor de un filtro seleccionado de 'Foto BI Inicial Detallada'[Fecha Foto] por el usuario, he intentado con MAX, MIN, y no me funciona... Gracias 1000Summarize table Lookupvalue in Funnel Visual
Hi Everyone, i have the below summarized table "Cover Type Analysis" as per the below : CoverTypeAnalysis = SUMMARIZE('Production ReportV20-V1 Power B','Production ReportV20-V1 Power B'[Cover Type],'Production ReportV20-V1 Power B'[Transaction Year],"Total Premium",SUM('Production ReportV20-V1 Power B'[Cedent Premium])) i have created a new column LastYear: LastYear = IFERROR(FORMAT(CoverTypeAnalysis[Transaction Year],"General Number") - 1,0) i need to calculate PercentageYearlyGrowthByCoverType: PercentageYearlyGrowthByCoverType = DIVIDE(CoverTypeAnalysis[Total Premium] - LOOKUPVALUE(CoverTypeAnalysis[Total Premium],CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type]), ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type])))/100 i have Transaction Year a Slicer Filter = 2019, everything looks wonderfull and the Funnel is looking correct: I was asked after that to add Quarter and Month Filters to this report, i added them to the summarized table : CoverTypeAnalysis = SUMMARIZE('Production ReportV20-V1 Power B', 'Production ReportV20-V1 Power B'[Cover Type], 'Production ReportV20-V1 Power B'[Transaction Year], 'Production ReportV20-V1 Power B'[Transaction Quarter], 'Production ReportV20-V1 Power B'[Transaction Month], "Total Premium",SUM('Production ReportV20-V1 Power B'[Cedent Premium])) and adjusted PercentageYearlyGrowthByCoverType to be as per the below: PercentageYearlyGrowthByCoverType = DIVIDE(CoverTypeAnalysis[Total Premium] - LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Transaction Quarter],CoverTypeAnalysis[Transaction Quarter], CoverTypeAnalysis[Transaction Month],CoverTypeAnalysis[Transaction Month], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type]), ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Transaction Quarter],CoverTypeAnalysis[Transaction Quarter], CoverTypeAnalysis[Transaction Month],CoverTypeAnalysis[Transaction Month], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type])))/100 however im getting wrong results as if it excecute the formula for each row and at the it sums all percentages while what i want is to make the calculation for all selected values: also i tried to create a mesure but it returned empty and couldnt load it into Funnel Visual PercentageYearlyGrowthByCoverType mesure = DIVIDE(SUM(CoverTypeAnalysis[Total Premium]) - LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],SELECTEDVALUE(CoverTypeAnalysis[LastYear]), CoverTypeAnalysis[Transaction Quarter],SELECTEDVALUE(CoverTypeAnalysis[Transaction Quarter]), CoverTypeAnalysis[Transaction Month],SELECTEDVALUE(CoverTypeAnalysis[Transaction Month]), CoverTypeAnalysis[Cover Type],SELECTEDVALUE(CoverTypeAnalysis[Cover Type])), ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],SELECTEDVALUE(CoverTypeAnalysis[LastYear]), CoverTypeAnalysis[Transaction Quarter],SELECTEDVALUE(CoverTypeAnalysis[Transaction Quarter]), CoverTypeAnalysis[Transaction Month],SELECTEDVALUE(CoverTypeAnalysis[Transaction Month]), CoverTypeAnalysis[Cover Type],SELECTEDVALUE(CoverTypeAnalysis[Cover Type]))))/100 appreciate your urgent assistance Thank you in advance Best regards, Georges Sabbagh
