Missing Codes

[Adavin]

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 is that if within the P&L there is no value against a code the Budget value does not update. I have manually enter the data for a code that has no actual, but has a budget value. But this is time consuming. 

Is there a way that I can say if a code has a budget value but no actual (from the P&L table) show this?

Your help is greatly appreciated.

Kindest 

Alice

[Mohamed32]

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.

[krishnakanth240 · Accepted solution]

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

[lbendlin]

Use the standard "Show items with no data" feature.