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:

     

jacinto's avatar
jacinto
Frequent Visitor
3 years ago

Thank you very much, it was immensly helpful! Ultimately I am trying to come up with one measure that works in different context. I calculated EmissionM for main table like below and then another formula to combine Emission from 202120 table and maint able(Total Emission) 

EmisionM =
SUMX(
    SUMMARIZE(
        factor,
        dim_item[item],
       "avf",AVERAGE( factor[factor]),
       
        "VolumeForFactorM",[Volume SoldM]
    ),
    [VolumeForFactorM]*[avf])
 
Emission Total = var selectedyr = IF(SELECTEDVALUE(dim_year[year])==2020 || SELECTEDVALUE(dim_year[year])==2021, TRUE(),FALSE())
        var selectedcrop = IF(SELECTEDVALUE(dim_item[item])=="soy" || SELECTEDVALUE(dim_item[item])=="wheat",TRUE(),FALSE())
        var tot=IF(selectedyr && selectedcrop,[Emmision],[EmmisionM])
        return tot
It works even though the total is not correct and I believe there must be better way of making it work under any context. 

 

jacinto's avatar
jacinto
Frequent Visitor
3 years ago

Any suggestions?