Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hola
Sé que la relación más beneficiosa con las tablas es siempre 1:n, que es la relación estándar factible a dimensionable. No estoy seguro de poder cambiar mi modelo de datos, ya que en realidad es mucho más complejo, pero aún así me gustaría saber cómo lidiar con esta relación en la que no puedo usar la función relacionada:
Tengo una tabla de datos con un MATERIAL ID de repetición:
Factable
ID de pedido | Material | Fecha | Fecha de finalización | |||
1234 | AAA | 01.01.2021 | 02.01.2021 | |||
1235 | BBB | 02.01.2021 | 03.01.2021 | |||
1236 | CCC | 03.01.2021 | 04.01.2021 | |||
1237 | DDD | 04.01.2021 | 05.01.2021 | |||
1238 | EEE | 05.01.2021 | 06.01.2021 | |||
1238 | AAA | 06.01.2021 | 07.01.2021 | |||
1238 | BBB | 07.01.2021 | 08.01.2021 | |||
1238 | CCC | 08.01.2021 | 09.01.2021 | |||
1238 | DDD | 09.01.2021 | 10.01.2021 | |||
1238 | EEE | 10.01.2021 | 11.01.2021 |
La tabla de datos está conectada con la tabla de filtros, que solo considera los materiales AAA, BBB y CCC:
Filtertable
Filtertable | WorkplaceID | |
AAA | TRT | |
BBB | TRT | |
CCC | TRT |
Y esta filtertable se conecta de nuevo con una tabla que consta de muchos tiempos de fabricación diferentes para cada material. Los tiempos y el código de proceso no se pueden agrupar en una fila, ya que a veces necesito diferentes códigos de proceso para diferentes cálculos de materiales, necesito dejarlos en sus respectivas filas propias:
Tiempo de fabricación
Material | Código de proceso | Hora1 | Hora2 | |||
AAA | 10 | 20 | ||||
AAA | 20 | 10 | 20 | |||
BBB | 10 | 100 | ||||
BBB | 20 | 50 | 100 | |||
CCC | 10 | 30 | 10 | |||
CCC | 20 | 10 |
Mi tarea es escribir una medida donde resuma la fabricación de los materiales en el facctable. Uno de los trabajos de la tabla de filtros es proporcionar conexiones solo para ciertos materiales. (La razón principal es que, desafortunadamente, los diferentes tiempos de proceso se encuentran en diferentes tablas de fabricación, dependiendo del tipo de material).
En una medida quiero sumar el Time1 con el código de proceso 20 f.
Y en otra medida necesito sumar el Tiempo 2 de ambos Processcodes.
Como tengo una relación n:1:n, no puedo usar la función relacionada.
Cada suma o código sumx que escribí termina teniendo cada fila con la misma cantidad mostrada o algo como la siguiente tabla.
Aparentemente, el filtrado no puede funcionar a través de relaciones n:1:n, por lo que el total no se resume:
ID de pedido | Material | Fecha | Fecha de finalización | MyMeasure | ||||
1234 | AAA | 01.01.2021 | 02.01.2021 | 50 | ||||
1235 | BBB | 02.01.2021 | 03.01.2021 | 50 | ||||
1236 | CCC | 03.01.2021 | 04.01.2021 | 50 | ||||
1237 | DDD | 04.01.2021 | 05.01.2021 | 50 | ||||
1238 | EEE | 05.01.2021 | 06.01.2021 | 50 | ||||
1238 | AAA | 06.01.2021 | 07.01.2021 | 50 | ||||
1238 | BBB | 07.01.2021 | 08.01.2021 | 50 | ||||
1238 | CCC | 08.01.2021 | 09.01.2021 | 50 | ||||
1238 | DDD | 09.01.2021 | 10.01.2021 | 50 | ||||
1238 | EEE | 10.01.2021 | 11.01.2021 | 50 | ||||
Total | 50 |
Nota Ya he activado el filtrado bidireccional en todas las tablas.
La ayuda es muy apreciada.
Muchas gracias de antemano.
Mejor.
Solved! Go to Solution.
@Applicable88 y @TheoC, advertiría CONTRA la dirección del filtro cruzado establecida en ambos a menos que esté seguro de que lo necesita.
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
No lo necesita en este escenario, solo necesita un poco de magia DAX para obtener el contexto correcto. SUMX hará el truco muy bien.
Puede que esta no sea la medida más eficiente para esto, pero hace el truco:
ACTUALIZADO: Lección IMPORTANTE: NO use SUM o funciones agregadas en columnas calculadas (que incluyen columnas calculadas virtuales como en el SUMX), ¡eso es lo que obtengo por responder a altas horas de la noche! He actualizado las siguientes medidas para que sean correctas ahora, ya que usan el CALCULATE para permitir el uso de agregar dentro de una columna.
@Applicable88 Hágame saber si este video tiene algún sentido o es útil. ¡Disculpas por mi solución descuidada anoche y espero que esto aclare un poco las cosas! 🙂
@Applicable88 Hágame saber si este video tiene algún sentido o es útil. ¡Disculpas por mi solución descuidada anoche y espero que esto aclare un poco las cosas! 🙂
@AllisonKennedy wow, felicitaciones para ti. Gracias por el gran apoyo y el maravilloso video de youtube. Muy bien explicado y aparentemente tienes mucha experiencia en explicar cosas! 🙂
@Applicable88,@TheoC a pesar de que @AllisonKennedy le dio una solución, la he ideado de una manera diferente y de la estructura actual, que no infla el tamaño del modelo (definitivamente lo haría en una situación, a menos que no lo necesite en absoluto).
En el modelo de datos actual, FILTERTbl filtra los filtros FactTbl y FILTERTbl ManufacturingTbl.
Está trayendo su eje de FactTbl en la Viz para este propósito y realizando la agregación en ManufacturingTbl.
Con la estructura actual, Fact no tiene forma de llegar a Manufacturing.
Sin necesidad de activar el filtrado bidireccional que introduce mucha ambigüedad en el modelo, el requisito mínimo para crear una relación indirecta entre Fact y Manufacturing (n:n) donde Fact Filters Manufacturing.
Una vez que tenga eso, debe escribir las siguientes medidas para alcanzar su objetivo.
_time1Manufacturing =
VAR _currentlyViisibleMaterialFromFact =
MAX ( FactTbl[Material] )
VAR _sumManufacturing =
CALCULATE (
SUM ( ManufacturingTbl[Time1] ),
TREATAS (
{ ( { _currentlyViisibleMaterialFromFact }, 20 ) },
ManufacturingTbl[Material],
ManufacturingTbl[ProcessCode]
),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
_sumManufacturing
_time2Manufacturing =
VAR _currentlyViisibleMaterialFromFact =
MAX ( FactTbl[Material] )
VAR _sumManufacturing =
CALCULATE (
SUM ( ManufacturingTbl[Time2] ),
TREATAS ( { _currentlyViisibleMaterialFromFact }, ManufacturingTbl[Material] ),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
_sumManufacturing
Además, si necesita esto en el nivel subtotal también. Hay dos versiones de subtotal y no estoy seguro de cuál necesitaría
_t1Subtotal1 =
SUMX (
ManufacturingTbl,
VAR _currentlyViisibleMaterialFromFact =
CALCULATE ( MAX ( FactTbl[Material] ) )
VAR _sumManufacturing =
CALCULATE (
SUM ( ManufacturingTbl[Time1] ),
TREATAS (
{ ( { _currentlyViisibleMaterialFromFact }, 20 ) },
ManufacturingTbl[Material],
ManufacturingTbl[ProcessCode]
),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
_sumManufacturing
)
_t2Subtotal1 =
SUMX (
ManufacturingTbl,
VAR _currentlyViisibleMaterialFromFact =
CALCULATE ( MAX ( FactTbl[Material] ) )
VAR _sumManufacturing =
CALCULATE (
SUM ( ManufacturingTbl[Time2] ),
TREATAS ( { _currentlyViisibleMaterialFromFact }, ManufacturingTbl[Material] ),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
_sumManufacturing
)
_t1Subtotal2 =
VAR _subTotalLevel =
SUMX (
ManufacturingTbl,
VAR _currentlyViisibleMaterialFromFact =
CALCULATE ( MAX ( FactTbl[Material] ) )
VAR _sumManufacturing1 =
CALCULATE (
SUM ( ManufacturingTbl[Time1] ),
TREATAS (
{ ( { _currentlyViisibleMaterialFromFact }, 20 ) },
ManufacturingTbl[Material],
ManufacturingTbl[ProcessCode]
),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
VAR _sumManufacturing2 =
CALCULATE (
SUM ( ManufacturingTbl[Time1] ),
FILTER (
ManufacturingTbl,
ManufacturingTbl[Material] = _currentlyViisibleMaterialFromFact
&& ManufacturingTbl[ProcessCode] = 20
),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
IF ( HASONEVALUE ( FactTbl[Material] ), _sumManufacturing1, _sumManufacturing2 )
)
RETURN
_subTotalLevel
_t2Subtotal2 =
VAR _subTotalLevel =
SUMX (
ManufacturingTbl,
VAR _currentlyViisibleMaterialFromFact =
CALCULATE ( MAX ( FactTbl[Material] ) )
VAR _sumManufacturing1 =
CALCULATE (
SUM ( ManufacturingTbl[Time2] ),
TREATAS ( { _currentlyViisibleMaterialFromFact }, ManufacturingTbl[Material] ),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
VAR _sumManufacturing2 =
CALCULATE (
SUM ( ManufacturingTbl[Time2] ),
FILTER (
ManufacturingTbl,
ManufacturingTbl[Material] = _currentlyViisibleMaterialFromFact
),
USERELATIONSHIP ( FactTbl[Material], ManufacturingTbl[Material] )
)
RETURN
IF ( HASONEVALUE ( FactTbl[Material] ), _sumManufacturing1, _sumManufacturing2 )
)
RETURN
_subTotalLevel
pbix se adjunta aquí
https://1drv.ms/u/s!AkrysYUHaNRvhcV23WX0LZIKtlCfpQ?e=xNzPcb
Wow, @smpa01 gracias por el esfuerzo también. Nunca vi Userelationship en acción. Veo que es necesario un código largo para llegar a 1. modelo de datos incorrecto y 2. sin relleno cruzado bidreccional.
¡Gracias por mostrarme alternativas!
Saludos.
@Applicable88 y @TheoC, advertiría CONTRA la dirección del filtro cruzado establecida en ambos a menos que esté seguro de que lo necesita.
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
No lo necesita en este escenario, solo necesita un poco de magia DAX para obtener el contexto correcto. SUMX hará el truco muy bien.
Puede que esta no sea la medida más eficiente para esto, pero hace el truco:
ACTUALIZADO: Lección IMPORTANTE: NO use SUM o funciones agregadas en columnas calculadas (que incluyen columnas calculadas virtuales como en el SUMX), ¡eso es lo que obtengo por responder a altas horas de la noche! He actualizado las siguientes medidas para que sean correctas ahora, ya que usan el CALCULATE para permitir el uso de agregar dentro de una columna.
@AllisonKennedy No sé cuándo escribiste la actualización... pero estoy agradecido de que lo hayas hecho. También descubrí, si solo hay sumx (facttable), sumx . . . sin envolver en el cálculo, el valor total general simplemente no es correcto.
¿Creo que es un problema de transición de contexto? Al menos eso supongo. Dado que no todos los materiales de la tabla de hechos están realmente representados en la tabla filtrada o de fabricación, necesitamos filtrar la columna de materiales en la tabla de hechos a través de la transición de contexto.
Muchas gracias.
Todo lo mejor.
@AllisonKennedy Solo por curiosidad, ¿por qué hiciste el esfuerzo de crear una nueva tabla y luego agregar una relación bidireccional / interdireccional si estás en contra?
@TheoC ¡Gran pregunta!
Solo estoy en contra de la relación bidireccional en muchas relaciones a una / una a muchas relaciones (y no soy solo yo, es una regla general ampliamente aceptada).
Con una relación de 1 a 1, no tienes otra opción, debe ser bidireccional. Esa relación esencial convierte 'FilterTable' y 'NewFilterTable' en una tabla. Solo un poco perezoso, así que no tenía ganas de fusionar el WorkplaceID en el NewFilterTable, pero eso nuevamente lo haría un poco más productivo en conjuntos de datos más grandes, por lo que potencialmente lo haría en Power Query y simplemente se desharía del 'FilterTable' en favor del 'NewFilterTable' que contiene todos los materiales y solo el WorkplaceID, etc, valores para los Materiales que los tienen.
@AllisonKennedy una vez más, no estoy seguro de cómo está utilizando las mejores prácticas si ha creado una tabla innecesaria y medidas adicionales para lograr el mismo resultado.
En términos de mi solución, funciona. No es eventual, es literal. Por ejemplo, las capturas de pantalla son de un PBIX que armé para crear una solución para @Applicable88. No estoy seguro de dónde de alguna manera has llegado a pensar que "eventualmente" funcionará cuando las capturas de pantalla sean la salida ...
De todos modos, gracias amablemente por sus aportes y sus puntos de vista sobre los elementos de las mejores prácticas. Que tengas un buen día 🙂
@TheoC Sus números son completamente diferentes a mi solución, no estoy tratando de pisar sus dedos de los ojos, solo tenía una interpretación diferente del problema y mientras estuve aquí, como siempre lo hago, traté de explicar POR QUÉ he hecho algo. Quería tener especial cuidado en explicar por qué mi solución era diferente a la ya proporcionada, ya que normalmente no publico en un hilo que ya tiene una respuesta. Sin embargo, había comenzado con esto antes y me lo quitaron, y desde entonces ha proporcionado una posible solución.
Su solución solo devuelve el valor firstnonblank y, como ha mencionado usted mismo en la publicación, no tiene en cuenta el código de proceso. Tiene razón: esta información no está en la FactTable. @Applicable88 especificó cómo necesitaban que esto se calculara.
Usando la captura de pantalla que tan útilmente proporcionó (arriba), en comparación con mis resultados (abajo), he resaltado los números de pedido correspondientes para ver las diferencias en nuestros cálculos:
Me olvidé de agregar Order ID a mis imágenes, así que lo he hecho ahora para ayudar a mostrar las diferencias en nuestras dos soluciones propuestas. Solo tendremos que esperar a que @Applicable88 nos haga saber si alguno de nosotros ha podido ayudar.
Una vez más, mis medidas funcionarán bien en su modelo tal como está, simplemente no puedo en buena conciencia proporcionar solo la mitad de una solución, lo que significa que si estoy proporcionando una medida a un modelo de datos que sé que podría causar problemas más abajo, diré algo.
Como he mencionado, no es mi opinión o una buena práctica para la bidireccional a muchas relaciones, causa ambigüedad en tu modelo y debes mantenerte alejado de él: https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
La tabla Dim era solo una ventaja, ver la última versión de adjunto que como fusionó las dos tablas dim en 1, deshaciéndose de la relación 1: 1 (aún podría funcionar con la tabla de filtro existente, pero eso hace que la vida sea más difícil de informar sobre los Materiales no presentes en esa tabla).
Aquí hay una captura de pantalla más grande de las diferencias entre sus capturas de pantalla y las mías:
PD, ¡me encanta la discusión!
Wow @AllisonKennedy Muchas gracias por su esfuerzo y explicación exhaustiva. Sabía que con ese modelo de datos sería más controvertido. Al igual que con muchas cosas en el "mundo real", muy a menudo no hay una estructura de datos perfecta ni un modelo de datos perfecto para una cosa determinada. Incluso si no hubiera FilteringTable, la tabla ManufacturingTime nunca se puede agrupar por, debido al diferente uso de Time1 y Time2 para diferentes procesos de fabricación de diferentes materiales. Y eso significa que terminaría de todos modos teniendo una relación m:n entre facttable y manufacturingTime table.
He descargado el archivo hace un momento. ¡Es tan interesante! Nunca vi tres sumx en sucesión, aquí solía filtrar el ProcessCode 20. Nunca se me ocurrió que se puede construir así.
Pero ya me encuentro con un problema con el que me enfrenté antes, pero esta vez de otra manera:
@AllisonKennedy Veo que en una selección no material no tenemos los "Totales" correctos:
solo después de la selección de UN material obtengo los totales correctos:
Después de agregar solo un material más, obtengo los totales equivocados:
lo mismo ocurre con la selección de todos los materiales:
Vi algunos tutoriales sobre "arreglar los totales", pero no creo que aborden el mismo problema. ¿Hay alguna manera de resolver esto también?
Muchas gracias hasta ahora. Aprendí mucho de eso.
@Applicable88 He eliminado mi recomendación ya que @AllisonKennedy ha reunido una solución que cumple con todos los requisitos.
@AllisonKennedy tiene todo el sentido! La solución que ha proporcionado definitivamente se alinea con lo que buscaba @Applicable88, ya que no leí la solicitud cuidadosamente.
En cuanto a los elementos bidireccionales, no podría estar más de acuerdo. Definitivamente es una mejor práctica. También me gusta el ajuste para eliminar la tabla adicional. ¡Bien hecho!
Definitivamente de acuerdo. La única razón por la que agregué la relación interdireccional fue para imitar la presentada por @Applicable88. Las salidas de mi solución siguen siendo las mismas sin necesidad de crear tablas adicionales, etc.
@TheoC Mi solución también funciona sin necesidad de crear tablas adicionales, solo tengo algo de tiempo libre hoy, así que hago un esfuerzo adicional para dar mis dos centavos (por lo que vale) sobre las mejores prácticas: la mesa Dim idealmente debería incluir TODOS los valores de los materiales, no solo algunos de ellos.
Su solución funcionará eventualmente, solo requiere columnas en lugar de medidas, por lo que es un poco menos dinámica, pero no estoy seguro de que sea un problema en este caso. Su solución también puede ser un poco más lenta en un conjunto de datos más grande simplemente porque está haciendo una búsqueda en lugar de depender de las relaciones integradas (que su solución no requiere en absoluto).
Lo que no creo que su solución haga (todavía) es tener en cuenta el Proceso: debe agregarlo a la función de filtro para tener en cuenta solo el Proceso 20, y luego también debe sumar el Proceso 20 y 30 para la otra medida requerida. 😀
Veo que has agregado otra respuesta hace un momento, así que iré a leer eso ...
Hola @AllisonKennedy,mientras creaba un conjunto de datos más realista para @TheoC según mi caso, acabo de probar su medida con la función de dos sumx. Como dijiste, también debería funcionar sin crear una nueva tabla.
https://drive.google.com/file/d/1MM8V_eBiD3GAAM3yswQQUKmle0WpAhQ6/view?usp=sharing
¡Curiosamente ahora los totales también son correctos! Antes y después del filtrado.
Por ejemplo, esa es de nuevo la tabla ManufacturingTime:
En este ejemplo quiero tener 4 Medidas diferentes:
1. Mostrar la suma de todo el tiempo de preparación de un material
2. Mostrar la suma de todo el ManuTime de un Material
3. Mostrar la suma del tiempo de preparación, pero solo de ProcessCode40
4. Mostrar la suma de Manutime, pero solo de ProcessCode30
Y, por supuesto, cada vez que los totales deben mostrar el valor sumado correcto.
Facttable sin Materialnumber seleccionado:
El primer PrepTime y ManuTime son las opciones básicas de suma de un campo entero.
PreptimeSum y ManutimeSum son Medidas a la suma (Preptime) y sum (ManuTime).
Como era de esperar, debido a la relación N:1:N, las primeras cuatro columnas de agregaciones no muestran el total correctamente, porque el modelo de datos no puede utilizar la función Related()-function ni nada. Y los tiempos se ubican "detrás" del otro lado de la relación 1:N.
Las dos últimas medidas son las interesantes:
Más Materialnumber seleccionado:
Los totales son absolutamente correctos en este momento, antes y después de filtrar los materiales.
@AllisonKennedy, ¿puede decirme qué está sucediendo realmente en la medida en la que sumx está envuelto en otro sumx, que puede encontrar el contexto de filtro correcto, mientras que las otras medidas solo lograron sumar los tiempos para cada materialmero una vez?
Y por qué esto, por ejemplo, no funcionará:
De hecho, he hecho un video, demasiado difícil de explicar por escrito!!!
Esperando a que se suba a Youtube, pero compartiremos el enlace en breve.
Cometí un error FATAL que ahora corregiré en mi publicación: NO use SUMX o SUM dentro de una columna calculada (esto también significa que no anida funciones SUMX a menos que coloque un CALCULATE a su alrededor).
Aquí está la fórmula correcta SI tiene una tabla de DIM_Material ADECUADA!!!!!!! (que no lo haces ahora mismo, lo explico en el video).
En el archivo adjunto (debajo de la firma) he actualizado para usar una tabla DimMaterial adecuada en lugar de la tabla de filtros, ¡pero háganos saber cómo le lleva!
@Applicable88,gracias por armar esto. Volveré a echar un vistazo a principios de la próxima semana cuando esté frente a la computadora nuevamente. Al decir esto, @smpa01 ha reunido algunas piezas sorprendentes de dax, así como ha modificado ligeramente las relaciones existentes del modelo de datos. Sin duda recomendaría darle una vuelta a esto para ver si logra lo que buscas. O bien wY, espero continuar esta conversación para encontrar una solución. Un talento increíble en este hilo en @smpa01 y @AllisonKennedy.
@TheoC, sí muchas gracias! Siempre es agradable ver diferentes formas alternativas. Aparentemente hay muchos de ellos en DAX ...
@AllisonKennedy No estoy seguro de por qué dijiste que he creado medidas. Tengo capturas de pantalla de las columnas calculadas en la solución. No estoy seguro de a dónde vas con todo esto...?