Forum Discussion

Adavin's avatar
Adavin
Frequent Visitor
3 months ago
Solved

Missing Codes

Hello Thank you for your time. I have an issue and would really appreciate your input.   I have two table 1) Budget data 2) P&L data The relationship is on the codes .i.e 1001, 1002   My issue...
Need Help
  • krishnakanth240's avatar
    krishnakanth240
    3 months ago

    Hi Adavin 

    Can you try creating calculated table

     

    Dim Code =

    DISTINCT(UNION(

    SELECTCOLUMNS(

    'Budget Finanancials Live 24-25 (24-25)',

    "Code",'Budget Finanancials Live 24-25 (24-25)'[Detail Code]),

    SELECTCOLUMNS(

    'P&L',"Code",

    'P&L'[Group1_Code])))

    Create relationship between Dim Code table to Budget and P&L table with one to many relationship

Mohamed32's avatar
Mohamed32
Advocate III
3 months ago

Create a separate dimension table for all unique codes and use it to connect both the Budget and P&L tables.

Dim Code =
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            'Budget Financials Live 24-25 (24-25)',
            "Code", 'Budget Financials Live 24-25 (24-25)'[Detail Code]
        ),
        SELECTCOLUMNS (
            'P&L',
            "Code", 'P&L'[Group1_Code]
        )
    )
)

Then create:

  • a one-to-many relationship from Dim Code[Code] to the Budget table
  • a one-to-many relationship from Dim Code[Code] to the P&L table

Finally, use the Code field from the Dim Code table in your visuals. This allows Power BI to show codes that have:

  • Budget only
  • Actuals only
  • or both

You may also need to enable Show items with no data in the visual.