CALCULATE() con Filter() en la tabla de muchos lados parece ignorar la dirección del relelationship

[Anonymous]

Hola

He publicado esta pregunta antes, pero por alguna razón sólo se aplicó al foro español y no al inglés (he comprobado), así que aquí va otro intento - y mis disculpas si 2 de las mismas preguntas deben aparecer.

La edición original

Me he encontrado con un caso en el que podría usar alguna ayuda para entender el comportamiento de la función CALCULATE(..., FILTER()), donde la instrucción FILTER() está filtrando la tabla en el lado de muchos de una relación de uno a varios y simplemente devolviendo todos los registros disponibles (IDs en este caso).

Considere el siguiente caso:

Una configuración muy sencilla con 2 tablas para realizar un seguimiento del progreso del proyecto, con una tabla que contiene las horas de presupuesto para cada proyecto'(Presupuesto de horas de proyecto') y un cuadro que contiene los informes reales del tiempo dedicado a los proyectos('Horas de proyecto gastadas'). En estas tablas hay una relación unidireccional que va de'Proyecto Presupuesto de horas' para Hours'Horas de proyecto gastadas'.

En esta configuración me gustaría crear una medida que calcule la suma de las horas de presupuesto para los proyectos que se han iniciado (donde se ha notificado el tiempo real invertido).

Esto (aparentemente) se puede hacer utilizando la siguiente medida:

Budget Hours Measure =
    CALCULATE(
        SUM('Projects Hours Budget'[Budget Hours]),
        FILTER(
            'Project Hours Spent',
            'Project Hours Spent'[Project ID]
        )
    )

La pregunta:
Me pregunto por qué esto funciona en primer lugar, ya que no hay ninguna relación que implique que'Horas de proyecto gastadas' debe filtrar'Presupuesto de horas de proyecto', que es la tabla en la que estamos haciendo un SUM(). La relación indica que la tabla Presupuesto puede filtrar la tabla Horas gastadas, pero no al revés. Además, si elimino la relación entre las tablas el resultado cambia (para producir la SUMA total como hubiera adivinado que sería en primer lugar), por lo que no hay duda de que la relación se está utilizando implícitamente de alguna manera - simplemente no sé por qué / cómo, y no he sido capaz de encontrar ninguna fuente que indique este comportamiento.

Básicamente, asumo que el motor está utilizando la relación existente para identificar columnas relacionadas y, a continuación, filtrar la tabla de horas de presupuesto mediante los identificadores de proyecto de la tabla Horas gastadas y al hacerlo ignorando la dirección de relación existente. Sólo espero que alguien pueda confirmar este comportamiento y espero que me vincule a alguna documentación o por igual.

A continuación se muestra una imagen de los resultados obtenidos mediante el uso de la medida anterior y el uso de un simple SUM(), así como una visión general del contenido de cada tabla:

EDITAR: basado en mi comentario más abajo me di cuenta de que incluir las consultas de DAX Studio sería ideal, por lo que aquí vienen:

1a consulta:

DEFINE TABLE '$TTable 2' :' 
SELECT
	SIMPLEINDEXN ( 'Projects Hours Budget'[Project ID] )
FROM 'Project Hours Spent'
	LEFT OUTER JOIN 'Projects Hours Budget' ON 'Project Hours Spent'[Project ID]-'Projects Hours Budget'[Project ID]
WHERE (
	 PFCASTCOALESCE ( 'Project Hours Spent'[Project ID] AS  INT ) <> 0 )

2a consulta:

DEFINIR LA TABLA '$TTable 1' :- 
SELECT
	SUM ( 'Proyectos Presupuesto de Horas'[Horas de Presupuesto] )
DESDE 
	'Proyectos Presupuesto de Horas'
DONDE
	'Proyectos Presupuesto de Horas' ININDEX '$TTable 2'[$SemijoinProjection];

[v-rzhou-msft]

Hola @Chris_7

Aquí construyo una nueva consulta denominada Query3 en comparación con otras dos consultas que compile antes.

Query 1 = 
    CALCULATE(
        SUM('Project Hours Budget'[Budget Hours]),
        FILTER(
            'Project Hours Spent',
            'Project Hours Spent'[Project ID]
        )
    )

Query 2 = 
SUM('Project Hours Budget'[Budget Hours])

Query 3 = 
    CALCULATE(
        SUM('Project Hours Budget' [Budget Hours]),
        FILTER(
            'Project Hours Budget',
            'Project Hours Budget'[Project ID] in VALUES('Project Hours Spent'[Project ID])
        )
    )

La consulta 3 volverá a la suma de las horas de presupuesto en la tabla de presupuesto de horas de proyecto filtradas. (ProjectID n.o 1,2,5,7 Todos los ID de proyecto están en el ID de proyecto en la tabla 'Horas de proyecto gastadas')

La tabla de presupuesto Horas de proyecto filtradas vuelve como se muestra a continuación.

Esto es lo mismo que Query1 (generar relaciones)

En la consulta 1 si crea una relación, la consulta funcionará como Query3, obtendrá todos los ID de proyecto en la tabla 'Project Hours Spent',

y use estos IDs para filtrar la tabla de presupuesto de horas de proyecto. Las devoluciones de la tabla de presupuesto Horas de proyecto filtradas son las mismas que las anteriores.

Por lo tanto, el resultado es 10+15+70+35 a 130.

Si quitamos la relación, Query1 funcionará como Query2. Debido a que dos tablas no están relacionadas, por lo que el filtro ID de proyecto en la tabla 'Horas de proyecto gastadas' no afectará a la tabla de presupuesto De horas de proyecto.

Por lo tanto, la tabla de presupuesto Horas de proyecto filtradas se devuelve a sí misma:

Y la Consulta1 es 10+15+8+9+70+40+35 a 187.

El resultado es el siguiente.

Construir relación:

Eliminar relación:

Puede descargar el archivo pbix desde este enlace: CALCULATE() con Filter() en la tabla de muchos lados parece ignorar la dirección del relelationship

Saludos

Rico Zhou

Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

[Anonymous]

Hola @RicoZhou

Gracias por la respuesta detallada - sin embargo, todavía me falta una explicación de por qué el motor utiliza la relación en primer lugar, dado que la dirección de la relación es de [Horas de presupuesto de proyectos] a [Horas de proyecto gastadas]. Esto implica naturalmente que [Horas presupuestarias de proyectos] debería ser capaz de filtrar [Horas de proyecto gastadas] - pero no al revés?

Teniendo en cuenta la dirección de la relación, ¿esperaría que el resultado de la consulta 1 siempre sea el mismo que la consulta 2?

[v-rzhou-msft]

Hola @Chris_7

¿Podría decirme si su problema ha sido resuelto? Si es así, acédi es la solución. Más gente se beneficiará de ello. O todavía está confundido al respecto, por favor proporcione más detalles sobre su problema o compártame con su archivo pbix de su Onedrive for Business.

Saludos

Rico Zhou

[Anonymous]

Hola @RicoZhou

Gracias por su respuesta - Sólo he tenido el tiempo para leer los artículos también, lo siento. Creo que he envuelto mi cabeza alrededor de la lógica que está pasando basado en su respuesta y el artículo sobre tablas expandidas.

Para resumir mi conclusión y espero que la verifiquen/desmienten:

Como pensé por primera vez, la dirección de la relación es de hecho ignorada en este cálculo, ya que la dirección va de [Presupuesto] a [Gastado] - y estoy tratando de calcular el [Horas de presupuesto] con un filtro basado en 'Horas de proyecto gastadas'[ID de proyecto]. Sin embargo, a medida que el cálculo se realiza mediante DAX, se crea implícitamente una tabla expandida basada en las relaciones existentes, y esta tabla expandida no tiene en cuenta la dirección de la relación.

Esta tabla expandida es igual a la que ha publicado una imagen, y el filtrado se realiza de hecho en esta tabla expandida - esto implica que la tabla expandida se limita a las filas donde 'Projects Hours Budget'[Project ID] - 'Project Hours Spent'[Project ID] (o es simplemente solo las filas donde hay un valor en 'Projects Hours Budget'[Project ID]?)

[v-rzhou-msft]

Hola @Chris_7

Si crea relaciones entre el presupuesto de horas de proyecto y las horas de proyecto gastadas por Id de porject.

La dirección de Uno a muchos es siempre de uno de los muchos.

Y en el motor hay una tabla de expansión como abajo.

Por lo tanto, la consulta 1 vuelve a 130, cuando se crean relaciones.

Eliminar relaciones allí es la Tabla A solamente.

Así que el filtro en la tabla B no tiene sentido, la consulta vuelve a 180.

Para obtener más información sobre las relaciones y la tabla de expansión, puede consultar estos blogs:

expanded-tables-in-dax

¿Cuál es la dirección de la relación en Power BI?

Saludos

Rico Zhou

Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

[amitchandak]

@Chris_7, como esto es, buscando verdadero y falso. Podría restringir a peoject ID, presente en la tabla horas. Así que creo que usted está recibiendo presupuesto para el proyecto disponible en horas

Proporcione sus comentarios y consejos para nuevos videos
Tutorial Series Dax Vs SQL Direct Query PBI Consejos
Apreciamos tus Felicitaciones.

[Anonymous]

Hola @amitchandak y gracias por la respuesta rápida.

Pero lo que me pregunto es por qué se está utilizando la relación en primer lugar, ya que Power BI obviamente filtra la tabla'Horas de presupuesto' basada en la tabla'Horas gastadas' devuelta de la instrucción FILTER() - y al mismo tiempo aparentemente ignora el hecho de que la relación entre esas tablas es un "uno a muchos" que va de la tabla "Horas de presupuesto" a la tabla "Horas gastadas".

¿Por qué se utiliza esta relación en primer lugar?

[amitchandak]

@Chris_7 , Power bi default es una combinación derecha. Así que obe usar cualquier cosa del hecho de que se considerará

[Anonymous]

@amitchandak podrías explicar cómo eso afecta a la relación en este caso?

[Anonymous]

Me siento un poco tonto de que no hice esto antes de crear la pregunta, pero a continuación está las consultas para el cálculo, obtenidas de DAX Studio (tenga en cuenta que esto no responde a mi pregunta principal sin embargo):

1a consulta:

DEFINE TABLE '$TTable2' := 
SELECT
	SIMPLEINDEXN ( 'Projects Hours Budget'[Project ID] )
FROM 'Project Hours Spent'
	LEFT OUTER JOIN 'Projects Hours Budget' ON 'Project Hours Spent'[Project ID]='Projects Hours Budget'[Project ID]
WHERE
	 ( PFCASTCOALESCE ( 'Project Hours Spent'[Project ID] AS  INT ) <> 0 ) ;

2a consulta:

DEFINE TABLE '$TTable1' := 
SELECT
	SUM ( 'Projects Hours Budget'[Budget Hours] )
FROM 
	'Projects Hours Budget'
WHERE
	'Projects Hours Budget'[Project ID] ININDEX '$TTable2'[$SemijoinProjection];

Por lo tanto, lo que hace es crear una tabla temporal con los valores en 'Presupuestode horasde proyectos'[ID de proyecto] que también existe en 'Horas de proyecto gastadas'[ID de proyecto]' y, a continuación, utiliza esta tabla temporal para filtrar la tabla 'Presupuesto de horas de proyectos' y después encontrar la suma de todas las [Horas de presupuesto].

Sin embargo, esto todavía deja mi pregunta abierta,en cuanto a por qué Power BI elige hacer este filtro / unirse en primer lugar?