The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I'm triying to do this measure
metas rotacion = var area = VALUES('prebel area_nomina'[abr])
return IF( VALUES('prebel empleados'[empresa])<>"PREBEL" && [% Retirados acumulados]>0 && [Retirados]>0;
0,3 ;
IF(NOT(ISERROR(area));
IF([Retirados acumulados]>0;
SWITCH(area;
"PA"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=83;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date]))));
"PI"; IF(ISERROR(VALUES('prebel cecos'[Procesos Rotacion PI])) && [% Retirados acumulados]>0 ;
CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=84;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date])))));
"PO"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=85;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date])))
)
;0)
)
)
)
But Power BI shows me an error "mDXsCRIPT(Model) (21 ,12) Calculation error in measure 'preber metas[metas rotacion]: A table of multiple values was supplied where a single value was expected"
What's wrong in my measure? Help me please
Solved! Go to Solution.
@smath,
This issue is caused by that you use VALUES() in your DAX. VALUES() funtion returns a one-column table that contains the distinct values from the specified table or column. Change your DAX to the following and check if you get expected result.
metas rotacion = var area = FIRSTNONBLANK('prebel area_nomina'[abr];1) return IF( FIRSTNONBLANK('prebel empleados'[empresa];1)<>"PREBEL" && [% Retirados acumulados]>0 && [Retirados]>0; 0,3 ; IF(NOT(ISERROR(area)); IF([Retirados acumulados]>0; SWITCH(area; "PA"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=83; FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date])))); "PI"; IF(ISERROR(FIRSTNONBLANK('prebel cecos'[Procesos Rotacion PI];1)) && [% Retirados acumulados]>0 ; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=84; FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date]))))); "PO"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=85; FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date]))) ) ;0) ) ) )
Regards,
Lydia
@smath,
This issue is caused by that you use VALUES() in your DAX. VALUES() funtion returns a one-column table that contains the distinct values from the specified table or column. Change your DAX to the following and check if you get expected result.
metas rotacion = var area = FIRSTNONBLANK('prebel area_nomina'[abr];1) return IF( FIRSTNONBLANK('prebel empleados'[empresa];1)<>"PREBEL" && [% Retirados acumulados]>0 && [Retirados]>0; 0,3 ; IF(NOT(ISERROR(area)); IF([Retirados acumulados]>0; SWITCH(area; "PA"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=83; FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date])))); "PI"; IF(ISERROR(FIRSTNONBLANK('prebel cecos'[Procesos Rotacion PI];1)) && [% Retirados acumulados]>0 ; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=84; FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date]))))); "PO"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=85; FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date]))) ) ;0) ) ) )
Regards,
Lydia
Hi Lydia, effectively this is a solution to my problem.
Thank u very much, was a super solutior.
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
157 | |
137 | |
132 | |
81 | |
61 |