topic Re: Getting All the values from one table to another using Dax Measure in DAX Commands and Tips

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

Rabi — Sun, 10 Dec 2023 23:30:17 GMT

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

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

parry2k — Mon, 11 Dec 2023 01:29:49 GMT

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

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

Rabi — Mon, 11 Dec 2023 02:18:04 GMT

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.

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

Rabi — Mon, 11 Dec 2023 22:34:28 GMT

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

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

parry2k — Tue, 12 Dec 2023 00:17:55 GMT

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