Forum Discussion

pdc's avatar
pdc
Frequent Visitor
9 years ago

link fact tables

  Hi,   Nooby with DAX/PowerBI. In SQL I know how to do, but I could not figure out how to do in Power BI desktop. In the screenshot from Excel I explain what I will achieve. Also the steps I see ...
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi pdc，

Based on your current sample data, you can create a new table using the following formula.

Newtable = ADDCOLUMNS('C dimension',"v1",LOOKUPVALUE(FACT1[v1],FACT1[c dim],'C dimension'[c-dim]),"a dim",LOOKUPVALUE(FACT1[a dim],FACT1[c dim],'C dimension'[c-dim]))

Then create relationship between new table and FACT2 table using c dim field, and create a new column in the newly created table using formula below.

Column = 'Newtable'[v1]*RELATED(FACT2[v2])

This way, you can create a table visual as follows.


For more details about the above steps, please review this attached PBIX file.

Thanks,
Lydia Zhang

pdc's avatar
pdc
Frequent Visitor
9 years ago

Hi Lydia,

 

Many thanks for your reply. I understand your solution. But I don't think this will solve my issue yet. If I'm correct the creation of Newtable is a static table during slice. This Newtable is refreshed during source data refresh. I need a dynamic table. I will explain why.

 

Based on your reply I understood my issue definition must be better. So I made a new screenshot, see below.

I added the g dim and r dim.

r dim was added because of a slicer, that excludes R1

g dim was added to show that v1 can be split into two values. Because there are a lot of records a summarize is required.

 

Does anyone have a solution? Many thanks in advance!

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi pdc,

    In this sceanrio,  you would need to create another table using the following formula.
    Table = SUMMARIZE(FACT1,FACT1[a dim],FACT1[c dim],"v1",sum(FACT1[v1]))

    Then create another new table based on the above table.

    Newtable = ADDCOLUMNS('C dimension',"v1",LOOKUPVALUE('Table'[v1],'Table'[c dim],'C dimension'[c-dim]),"a dim",LOOKUPVALUE('Table'[a dim],'Table'[c dim],'C dimension'[c-dim]))

    Please review modified PBIX file.

    Thanks,
    Lydia Zhang