Como relacionar dos tablas

[msae26]

Hola buenas días a todos

Soy nuevo en PowerBI. Tengo estas dos datablas que quisiera relacionar, pero no sé como. Espero me puedan ayudar:

tabla1

tabla2

Como podria relacionar estas dos tablas?

Muchas gracias de antemano a los que puedan dar ideas.

[msae26 · Accepted solution]

Hi again everyone

I tried with another dax code. Insted of use SUM inside CALCULATE I used SUMX, like this:

PCI = CALCULATE(
    SUMX('Potere Calorico Inferiore', 'Potere Calorico Inferiore'[PCI Coge MJp/Smc]),
        FILTER(ALL('Potere Calorico Inferiore'),
            'Potere Calorico Inferiore'[anno]='Test'[Data].[Year] && 'Potere Calorico Inferiore'[mese]='Test'[Data].[MonthNo])
)

This seems more logic for me, since SUMX is iterative function. I keep waiting for someone who can help me to clean things up.

Great day  for all

[Icey · Accepted solution]

Hi @msae26 ,

Try this:

PCI =
LOOKUPVALUE (
    'Potere Calorico Inferiore'[PCI Coge MJp/Smc],
    'Potere Calorico Inferiore'[anno], 'Produzione da Cogenerazione'[Date].[Year],
    'Potere Calorico Inferiore'[mese], 'Produzione da Cogenerazione'[Date].[MonthNo]
)

Best Regards,

Icey

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

[msae26 · Accepted solution]

Hi again everyone

I tried with another dax code. Insted of use SUM inside CALCULATE I used SUMX, like this:

PCI = CALCULATE(
    SUMX('Potere Calorico Inferiore', 'Potere Calorico Inferiore'[PCI Coge MJp/Smc]),
        FILTER(ALL('Potere Calorico Inferiore'),
            'Potere Calorico Inferiore'[anno]='Test'[Data].[Year] && 'Potere Calorico Inferiore'[mese]='Test'[Data].[MonthNo])
)

This seems more logic for me, since SUMX is iterative function. I keep waiting for someone who can help me to clean things up.

Great day  for all

[Icey · Accepted solution]

Hi @msae26 ,

Try this:

PCI =
LOOKUPVALUE (
    'Potere Calorico Inferiore'[PCI Coge MJp/Smc],
    'Potere Calorico Inferiore'[anno], 'Produzione da Cogenerazione'[Date].[Year],
    'Potere Calorico Inferiore'[mese], 'Produzione da Cogenerazione'[Date].[MonthNo]
)

Best Regards,

Icey

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

[msae26 · Accepted solution]

Hi Icey

Thanks so much for your support. I've alredy solve my problem using a CALENDAR table and related all my other tables to it. Anyway thanks so much.

[msae26]

Hi everyone

At the end I solve my challnege doing the following:

1. I deleted the relationship between the tables 1 and 2.
2. Then I used this dax:

PCI = CALCULATE(SUM('Potere Calorico Inferiore'[PCI Coge MJp/Smc]),
        FILTER(ALL('Potere Calorico Inferiore'),
            IF('Potere Calorico Inferiore'[anno]= 'Produzione da Cogenerazione'[Data].[Year] && 'Potere Calorico Inferiore'[mese]='Produzione da Cogenerazione'[Data].[MonthNo],
                'Potere Calorico Inferiore'[Indice], BLANK())
        )
)
        ​

With that dax code I could get what i wanted.  Create and polulate the table 3 with the value PCI from table 1.

But I have a dubt, I really do not why I should put SUM, since with SUM I get the sum of the whole column and here

I do not need the sum. The rest of the code is ok, cause is the filter I need for. Maybe some can

help me to understand.

Thanks

[amitchandak]

@msae26 , you can create a common date table and analyze the data together with common date table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[msae26]

Hi amitchandak

Now, I have my two tables related as your advise.

Table_1: "Potere Calorifico Inferiore"

Tablen_2: Calendar

Now that I have all this tables related, I'd like to know how to create another calculated table, table_3 ("

Produzione da Cogenerazione"), where I need to pick the PCI value from table_1 and populate table_3.

Table_3 is like this:

I hope you can help.

Best regards