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 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

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

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 months ago

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

     

     

     

     

  • krishnakanth240's avatar
    krishnakanth240
    Super User
    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.