Forum Discussion

jacinto's avatar
jacinto
Frequent Visitor
3 years ago
Solved

Valid measure for two fact tables

Hi all,   I have two fact tables that couldn't be merged.  The following is dummy data and sample content. The Main table contain most of the data, however, I have also small table with extra infor...
Need Help
Show and Tell
  • bolfri's avatar
    bolfri
    3 years ago

    dim_item

    dim_item = 
DISTINCT(
    UNION(
        GROUPBY('202120','202120'[item]),
        GROUPBY(factor,factor[item]),
        GROUPBY(main,main[item])
    )
)

     

    dim_origin

    dim_origin = 
DISTINCT(
    UNION(
        GROUPBY('202120','202120'[origin]),
        GROUPBY(factor,factor[origin])
    )
)

     

    dim_year

    dim_year = 
DISTINCT(
    UNION(
        GROUPBY('202120','202120'[year]),
        GROUPBY(main,main[year])
    )
)

    Relationship

     

    Volume Sold = SUM('202120'[volume ])
    Emmision =
    SUMX(
        SUMMARIZE(
            factor,
            dim_item[item],
            factor[factor],
            dim_origin[origin],
            "VolumeForFactor",[Volume Sold]
        ),
        [VolumeForFactor]*factor[factor]
    )
     
    Result:

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

jacinto , if these measure are coming from two fact use common dimension to sum up

 

like

 

Sumx(Item , calculate( [totalvolume]*[average factor]) )

 

where totalvolume and average factor are measure from two facts

 

 

jacinto's avatar
jacinto
Frequent Visitor
3 years ago

But factor is only on the DM table not on the facts table. Aslo volume exisits in both Main table and 202120 table for 2021 and 2021 (basically 202120 table is subset of Main table with origin value known, therefore volume should be multiplied with factor based on the origin). I hope I managed to explain.