Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Se creó una sola medida para rellenar filas basadas en el envejecimiento (Buckets 1-6) y la hoja de cálculo ([2], [7] y CVTY [7]).
Medida = IF('Scorecards Merged data'[AGE#] = 2,IF('Scorecards Merged data'[AgeNumber] = 2 && 'Scorecards Merged data'[AGE#] = 2 && NOT('Scorecards Merged data'[Spreadsheet],"CVTY")), 'Scorecards Merged data'[COUNT(*)], IF('Scorecards Merged data'[AGE#] = 2 && 'Scorecards Merged data'[Age Number] = 2 && CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"), 'Scorecards Merged data'[COUNT(TOPICID)], IF(CALCULATE(SUM('Scorecards Merged data'[COUNT(*)]),ALLEXCEPT('Scorecards Merged data','Scorecards Merged data'[ReceivedDate],'Scorecards Merged data'[AGE#], 'Scorecards Merged data'[AgeNumber]),CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[2]"),'Scorecards Merged data'[AgeNumber] = 2, 'Scorecards Merged data'[AGE#] = 2) <= CALCULATE (SUM('Scorecards Merged data'[COUNT(TOPICID)]),ALLEXCEPT('Scorecards Merged data','Scorecards Merged data'[ReceivedDate],'Scorecards Merged data'[AGE#], 'Scorecards Merged data'[Age Number]) ,CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"),'Scorecards Merged data'[Age Number] = 2, 'Scorecards Merged data'[AGE#] = 2), 0, 'Scorecards Merged data'[COUNT(*)] - CALCULATE(SUM('Scorecards Merged data'[COUNT(TOPICID)]),ALLEXCEPT('Scorecards Merged data','Scorecards Merged data'[ReceivedDate],'Scorecards Merged data'[AGE#], 'Scorecards Merged data'[Age Number]),CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"),'Scorecards Merged data'[Age Number] = 2, 'Scorecards Merged data'[AGE#] = 2)))),0)
Las columnas de Cubo de Power BI de salida 1-6 son correctas para [2] y [7], pero para CVTY [2], la diffrence a veces es correcta y a veces incorrecta... no estoy seguro de dónde está mi desconexión dentro de la medida.
COUNT(TOPICID) | COUNT(*) | ReceivedDate | AgeNumber | AGE_GROUP | hoja de cálculo | Cubo 1 | Cubo 2 | Cubo 3 | Cubo 4 | Cubo 5 | Cubo 6 | Número de edad | AGEGRP | edad # |
0 | 5/15/2021 | PCS_CVTY[2] | 0 | 0 | 0 | 0 | 0 | 0 | ||||||
0 | 1 | 5/15/2021 | 6 | > más de 90 días | PCS_CVTY [2] | 0 | 0 | 0 | 0 | 0 | 1 | 6 | ||
0 | 3 | 5/15/2021 | 2 | >= 2 días < 15 días | PCS_CVTY[2] | 0 | -2 | 0 | 0 | 0 | 0 | 2 | ||
0 | 1 | 5/15/2021 | 1 | < de 2 días | PCS_CVTY [2] | 0 | 0 | 0 | 0 | 0 | 0 | 1 | ||
0 | 3 | 5/15/2021 | 3 | >= 15 días < 30 días | PCS_CVTY [2] | 0 | 0 | 2 | 0 | 0 | 0 | 3 | ||
0 | 4 | 5/15/2021 | 4 | >= 30 días < 60 días | PCS_CVTY [2] | 0 | 0 | 0 | 4 | 0 | 0 | 4 | ||
1 | 5/15/2021 | PCS [7] | 0 | 0 | 1 | 0 | 0 | 0 | 3 | >= 15 días < 30 días | 3 | |||
4 | 5/15/2021 | PCS [7] | 0 | 4 | 0 | 0 | 0 | 0 | 2 | >= 2 días < 15 días | 2 | |||
6 | 5/15/2021 | PCS [7] | 6 | 0 | 0 | 0 | 0 | 0 | 1 | < de 2 días | 1 | |||
0 | 1 | 5/15/2021 | 6 | > más de 90 días | PCS [2] | 0 | 0 | 0 | 0 | 0 | 1 | 6 | ||
0 | 3 | 5/15/2021 | 2 | >= 2 días < 15 días | PCS [2] | 0 | 3 | 0 | 0 | 0 | 0 | 2 | ||
0 | 1 | 5/15/2021 | 1 | < de 2 días | PCS [2] | 1 | 0 | 0 | 0 | 0 | 0 | 1 | ||
0 | 3 | 5/15/2021 | 3 | >= 15 días < 30 días | PCS [2] | 0 | 0 | 3 | 0 | 0 | 0 | 3 | ||
0 | 4 | 5/15/2021 | 4 | >= 30 días < 60 días | PCS [2] | 0 | 0 | 0 | 4 | 0 | 0 | 4 | ||
0 | 5/15/2021 | PCS [2] | 0 | 0 | 0 | 0 | 0 | 0 | ||||||
0 | 5/15/2021 | PCS [7] | 0 | 0 | 0 | 0 | 0 | 0 |
Solved! Go to Solution.
Cerrando este tema como me di cuenta de una solución extensa para obtener los valores deseados para rellenar la matriz.
Cerrando este tema como me di cuenta de una solución extensa para obtener los valores deseados para rellenar la matriz.
El cálculo NO es una medida, sino una columna calculada. El siguiente DAX funciona para un proveedor singluar (hay tres), para cada una de las seis columnas bucket, pero ¿cómo cambio el DAX para ver cada proveedor según corresponda dentro de cada columna?
Bucket 2 = IF('Scorecards Merged data'[AGE#] = 2 && 'Scorecards Merged data'[Age Number] = 2 &&CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"), 'Scorecards Merged data'[COUNT(TOPICID)], IF('Scorecards Merged data'[AGE#] <> 2, 0, IF('Scorecards Merged data'[AGE#] = 2 && 'Scorecards Merged data'[AgeNumber] = 2 &&&NOT(CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"CVTY")) && CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[2]"), 'Scorecards Merged data'[COUNT(*)], IF('Scorecards Merged data'[AgeNumber] = 2 &&&CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"CVTY") && 'Scorecards Merged data'[COUNT(*)] - CALCULATE(SUM('Scorecards Merged data'[COUNT(TOPICID)]),ALLEXCEPT('Scorecards Merged data','Scorecards Merged data'[ReceivedDate],'Scorecards Merged data'[AGE#] , 'Scorecards Merged data'[Vendor], 'Scorecards Merged data'[Age Number]),CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"),'Scorecards Merged data'[Age Number] = 2, 'Scorecards Merged data'[Vendor] = "Vendor1", 'Scorecards Merged data'[AGE#] = 2) <=0, 0, 'Scorecards Merged data'[COUNT(*)] - CALCULATE(SUM('Scorecards Merged data'[COUNT(TOPICID)]),ALLEXCEPT('Scorecards Merged data','Scorecards Merged data'[ReceivedDate],'Scorecards Merged data'[AGE#], 'Scorecards Merged data'[Vendor], 'Scorecards Merged data'[Age Number]),CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"),'Scorecards Merged data'[Age Number] = 2, 'Scorecards Merged data'[Vendor] = "Vendor1", 'Scorecards Merged data'[AGE#] = 2)))))
Tabla de matriz...
fecha | vendedor | Cubo 1 | Cubo 2 | Cubo 3 | Cubo 4 | Cubo 5 | Cubo 6 |
5/22/2021 | Vendor2_Special | 0 | 0 | 0 | 0 | 0 | 0 |
5/22/2021 | Proveedor2[2] | 0 | 5 | 5 | 4 | 0 | 1 |
5/22/2021 | Proveedor2[CVTY] | 0 | 3 | 5 | 4 | 0 | 1 |
5/22/2021 | Proveedor2[7] | 7 | 5 | 2 | 0 | 0 | 0 |
5/22/2021 | Proveedor3[2] | 0 | 1 | 0 | 1 | 1 | 2 |
5/22/2021 | Proveedor3[CVTY] | 0 | 0 | 0 | 1 | 1 | 2 |
5/22/2021 | Vendor3_Special | 0 | 0 | 0 | 0 | 0 | 0 |
5/22/2021 | Proveedor3[7] | 0 | 0 | 0 | 0 | 0 | 0 |
5/22/2021 | Proveedor1[2] | 0 | 1 | 1 | 0 | 0 | 0 |
5/22/2021 | Proveedor1[CVTY] | 0 | 0 | 1 | 0 | 0 | 0 |
5/22/2021 | Vendor1_Special | 0 | 0 | 0 | 0 | 0 | 0 |
5/22/2021 | Proveedor1[7] | 0 | 2 | 0 | 0 | 0 | 0 |
Las columnas del bucket son columnas calculadas, NO una medida. Age#, AgeNumber y Age Number cambian para cada columna de bucket calc. He reducido mi cubo 1 - 6 DAX para...
IF('Scorecards Merged data'[AGE#] = 2 && 'Scorecards Merged data'[Age Number] = 2 && CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"), 'Scorecards Merged data'[COUNT(TOPICID)], IF('Scorecard Merged data'[Age#] <> 2, 0, IF('Scorecards Merged data'[AGE#] = 2 && 'Scorecards Merged data'[AgeNumber] = 2 && CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[2]") &&NOT('Scorecards Merged data'[Spreadsheet],"CVTY")), 'Scorecards Merged data'[COUNT(*)], [Calc CVTY]))
Hay tres proveedores cada uno con las mismas tres hojas de cálculo. Para cada proveedor, el [COUNT(TOPICID)]... [7] y [COUNT(*)]... [2] los recuentos son correctos, la diferencia entre los dos es donde estoy fallando. He añadido la medida [Calc CVTY] al cálculo y funciona ya que necesito que funcione para la diferencia, pero solo para "VendorA" y bucket 2 a medida que se produce la dependencia circular para los otros buckets. ¿Se puede transformar la medida [Calc CVTY] para trabajar para cada cubo... o qué cálculo DAX puedo agregar para calcular correctamente la diferencia para los seis buckets para cada proveedor.
Calc CVTY =
IF(SUM('Scorecards Merged data'[COUNT(*)]) <= CALCULATE(SUM('Scorecards Merged data'[COUNT(TOPICID)]), ALLEXCEPT('Scorecards Merged data', 'Scorecards Merged data'[ReceivedDate], 'Scorecards Merged data'[AGE#], 'Scorecards Merged data'[Vendor], 'Scorecards Merged data'[Age Number]), CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"), 'Scorecards Merged data'[Age Number] = 2, 'Scorecards Merged data'[AGE#] = 2, 'Scorecards Merged data'[Vendor] = "VendorA"), 0, SUM('Scorecards Merged data'[COUNT(*)]) - CALCULATE(SUM('Scorecards Merged data'[COUNT(TOPICID)]), ALLEXCEPT('Scorecards Merged data', 'Scorecards Merged data'[ReceivedDate], 'Scorecards Merged data'[AGE#], 'Scorecards Merged data'[Proveedor], 'Scorecards Merged data'[Age Number]), CONTAINSSTRING('Scorecards Merged data'[Spreadsheet],"[7]"), 'Scorecards Merged data'[Número de edad] = 2, 'Scorecards Merged data'[AGE#] = 2, 'Scorecards Merged data'[Vendor] = "VendorA")
HEy @N823198 ,
hazte un favor y usa SWITCH(TRUE()) y definitivamente usa el FORMAtter DAX. Entonces debería ser posible entender la medida.
A continuación, siguiente pregunta, publicó una medida y se refiere a 6 columnas. ¿Esa medida se usa 6 veces? ¿Son columna calculada? ¿Cómo cambia la medida entre los 6 usos?
Hazlo lo más fácil posible, entonces la gente puede ayudarte.
Saludos
Denis
Todavía no he utilizado el Switch(True()) o DAX Formatter, así que tendré que leerlos.
Sí, la medida se utiliza 6 veces para cada columna calculada, siendo el único cambio age#, AgeNumber y Age Number para referencia al "bucket". El ejemplo original anterior es la medida utilizada para la columna Bucket 2. La medida utilizada para la columna Bucket 3 es exactamente la misma que la medida para la columna Bucket 2, la excepción de AGE#, AgeNumber y Age Number en la medida es "3"... y así sucesivamente para las otras medidas de columna bucket.