Criteria for when and how to use fields from multiple tables in DAX

[Syndicate_Admin]

Hello,

I've been using PowerBI for some time now and I always end up finding the solution I need to solve the calculation needs I have. However, I realize that I am not understanding well and base how the relationships between various tables work at the level of DAX operations.

I am forced to work constantly with values that are distributed in different tables and that I need to appear in the same calculation, and sometimes I manage to link them without problem and, in others, I do not understand why it is not possible for me to "call" certain values that are distributed, despite having related them.

I am clear about how to relate the different tables, and that there is usually a direction in that relationship, but for example, I would like to understand on what occasions although there is a relationship, it is not possible to perform DAX calculations with certain values.

For example, I have related Table A (1) through one column, with two tables; B(*) and C(*). However, from table A I am not being able to generate measures or columns that use values contained in the other two tables.

On the other hand, I also do not understand well when it is more appropriate to use CALCULATE with a filter, vs when it is enough to use the FILTER function.

Greetings and thank you very much,

[v-zhangti]

Hi, @Syndicate_Admin 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Good morning

For the case I mention as an example, I do not know if the following helps:

I need to calculate how many days of difference there are between the Fecha_INICIO and the Fecha_CIERRE of a revalidation operation performed on different machines.

These two dates are contained in two different tables, which we will call Tabla_B and Tabla_C:

Tabla_B:

HOSTNAME	Fecha_INICIO	STATE	Columna_4	…
H1	22/09/22	Current
H2	29/09/22	Current
H1	02/03/22	Closed

Tabla_C:

HOSTNAME	Fecha_CIERRE	Columna_3	Columna_4	…
H1	22/03/22
H2	29/04/22
H1	02/04/22

In the Fecha_INICIO, the date on which the operation has been initiated is reported, and in the Fecha_CIERRE, the date on which the last closure related to the revalidation of the dats has been reported (it can be from the previous iteration of revalidations).

Between the HOSTNAME columns, PowerBI does not let me establish the relationship between columns, because in neither of them does it find unique values, since the HOSTNAME value can be repeated to identify several revalidations on the same machine.

Therefore, I resort to a third table, the Tabla_A, which contains many columns, but one of them is that of HOSTNAME without duplicates.

Thus, the relationships are drawn as follows through the HOSTNAME columns:

Tabla_A (1) ------> Tabla_B (*)

Tabla_A (1) ------> Tabla_C (*)

From here, I can not make the following comparison: If between Fecha_INICIO and Fecha_CIERRE more than 30 days have passed and the revalidation remains "In progress", I want to determine that it is taking too long and that it is "NOT OK". Otherwise, if it's within 30 days or already closed, it's "OK."

To do this, I am not sure where to propose the calculation.

I have tried it from Table A, creating a new column, but it does not detect the fields Tabla_B[FECHA_INICIO], nor Tabla_C[FECHA_CIERRE].

I also haven't been able to perform the calculation through a measure, although it doesn't make sense unless I do the following to create values associated with each HOSTNAME:

Aux Start Date=SUM(Tabla_B[FECHA_INICIO])

Aux CLOSING DATE=SUM(Tabla_C[FECHA_CIERRE])

So PowerBI lets me call these values, but I don't quite understand why in the case of the column in the Tabla_A it doesn't work, for example.

On the other hand, if I use CALCULATE there are columns that I cannot access for calculations, while when I create calculations with FILTER, yes.

I do not know if this information makes my question a little clearer.

Greetings and thank you very much,