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 ...
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] )
    )
)