Forum Discussion

AngelaB's avatar
AngelaB
Helper I
2 years ago
Solved

Combining data from two tables when data is in different format

Hello fabulous Fabric community. I will do my best to describe my problem to see if what I'm trying to do is even possible. I have also included some sample data below to show what I mean.   I have...
  • Anonymous's avatar
    Anonymous
    2 years ago

    HI AngelaB,

    Here is the calculated table formula to union two table and convert their records to same format, you can try to use it if suitable for your requirement.

     

    Merged =
UNION (
    SELECTCOLUMNS (
        'Table1',
        'Table1'[Ward],
        'Table1'[Timepoint],
        "COG", 'Table1'[%COG],
        "MOB", 'Table1'[%MOB],
        "NUTR", 'Table1'[%NUTR]
    ),
    SUMMARIZE (
        'Table2',
        'Table2'[Ward],
        'Table2'[Timepoint],
        "COG",
            DIVIDE (
                CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[COG] = "Yes" ),
                COUNTROWS ( 'Table2' )
            ),
        "MOB",
            DIVIDE (
                CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[MOB] = "Yes" ),
                COUNTROWS ( 'Table2' )
            ),
        "NUTR",
            DIVIDE (
                CALCULATE ( COUNTROWS ( 'Table2' ), 'Table2'[COG] = "Yes" ),
                COUNTROWS ( 'Table2' )
            )
    )
)

     

    Regards,

    Xiaoxin Sheng

AngelaB's avatar
AngelaB
Helper I
2 years ago

Hello Anonymous - thank you so much for this solution (and sorry for the delayed response, leave over the Festive period and all that).

 

I have implemented this using a new calculated table and it appears to have worked (no DAX errors etc), however the resultant merged table is patchy... some of the pre-2022 data has come through and some of the post-2022 data has come through, but there are gaps for both which is really odd. Any thoughts on what would cause a glitch like that?

 

Thanks again, Angela.