Getting All the values from one table to another using Dax Measure

[Rabi]

Hi Everyone, 

Below is then dummy data model, how can i get the budget amount for GAS although there is no transaction in that account in  the General Legder using measure. 

I have matrix visual, I have used account code and Name in rows from ACCOUNTS table, and below is my measure for getting budget, 

Budget = 
VAR Budget = CALCULATE (
    SUM ( budget table[Budget] ),
    Budget table[month]= MAX ( General Ledger[month] ),
    Budget table[Year]= MAX ( General Ledger[Year] ),
    ALL ( Budget table[Account Code])
)

can you please help me to get the result below:

Thanks Verymuch for help !!

[parry2k]

@Rabi seems like the dim date table has a relationship with the account table. Why that is there?

[parry2k]

@Rabi why you have the following code:

  Budget table[month]= MAX ( General Ledger[month] ),
    Budget table[Year]= MAX ( General Ledger[Year] ),

If there is a date column, then add a date dimension and set the relationship of this date dimension with both GL and Budget table, and the measure should be a simple sum budget.

Also, in the output visual you have shown, you cannot have the same account code row twice until there is another column to make it a unique row.

See

[Rabi]

Hi @parry2k , I tried creating relation from Dim DATE table with Budget table and GL table but it doesnt allow me. It says there will be ambiguty between Account table and DIM date

[Rabi]

Hi @parry2k , I have those column in both General ledger and Budget table, I dint wanted to clutter the dummy model that is why i didnt include the date columns, I am using Fiscal Year and Fiscal Period as dates column in both of the tables.

Also my mistake in the output table, there is no duplicate acccount code.