Forum Discussion

MathiasBI's avatar
MathiasBI
Regular Visitor
3 years ago

Using a date column to filter two columns from different tables

Hi!

 

I'm managing a Power BI report where this company shows their budget versus actual numbers. 

There are three important columns; AccountNo, Budget and Actual.

Budget and Actual are from two different views, while AccountNo is the key connector between them. 

 

I need a separate date column which can filter these numbers in month and years. I haven't been able to create a column where I get the correct numbers in both columns. It's always either or.

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Create a proper date table, marked as a date table, and link that to both budget and actual tables. Use columns from your date table in your visuals and it should filter everything correctly.

    It would also be a good idea to create a separate dimension table for your accounts and link that to both budget and actual tables. It sounds like at the moment you have a many-to-many relationship between the two and that is rarely a good idea.

    You can create a table based on the unique values using something like

    Account Number =
DISTINCT (
    UNION (
        DISTINCT ( 'Budget'[Account Number] ),
        DISTINCT ( 'Actual'[Account Number] )
    )
)