topic Re: Difference between two tables in DAX Commands and Tips

Difference between two tables

Ireneliu — Wed, 19 Jun 2019 02:53:49 GMT

Hello Everyone,

I have a question about the diference between two tables.

A Table is for department's actual results.

B Table is for department's budget.

When the actual results have a value, I can't get a correct value, even no value.

I want to have values by month.

Please suggest the DAX measure.

Thank you!

Re: Difference between two tables

Stachu — Wed, 19 Jun 2019 15:35:11 GMT

can you share your current syntax, and the underlying tables?

as described here

Re: Difference between two tables

Ireneliu — Thu, 20 Jun 2019 02:15:46 GMT

Hello Stachu ,

It was my first time publishing a question here, sorry for the incomplete information.

Columns for Table A are Department、AccountName、Date、accountingcurrencyamount

Columns for Table B are Department、AccountName、Date、budget

The relationship between two tables is "AccountName".
(Cardinality:many to many. Cross filter direction:both.)

*Measure for actual results = CALCULATE(SUM('A'[accountingcurrencyamount]),

FILTER('A',[AccountName]="Salary"|| [AccountName]="Retirement pension")

*Measure for budget = CALCULATE(SUM('B'[departmentbudget]),

FILTER('B',[AccountName]="Salary"|| [AccountName]="Retirement pension")

I used "FILTER" to choose the AccountName I need.

*Measure for difference = [Measure for actual results]-[Measure for budget]

I tried to change the relationship become Date yesterday, and got the following result.

I still can't get the right values when actual results have value...

Re: Difference between two tables

Stachu — Thu, 20 Jun 2019 05:02:03 GMT

do the following:

1) create a new table for calendar, e.g.

Calendar = CALENDARAUTO()

2) mark the Calendar tables as Date table
3) create 1:many joins between Calendar table and both budget and actuals

4) use the month from calendar table in the visual

5) set the join between A & B tables to be done on Department

Re: Difference between two tables

Ireneliu — Fri, 21 Jun 2019 02:49:17 GMT

I followed up the steps you suggested but having a trouble in step 3.

The relationship between Calendar table and both budget and actuals can't be active at the same time.

Re: Difference between two tables

Stachu — Sat, 22 Jun 2019 10:12:53 GMT

Hmm, that's probably because of the many:many relationship

can you add a new table for Departments, e.g. like this:

Departments =
DISTINCT (
    UNION ( VALUES ( TableA[Departments] ), VALUES ( TableB[Departments] ) )
)

deactivate/remove your existing many:many relationship between Actuals and Budgets

create new 1:many relationships from Departments to tables A and B

do the same for the Calendar table

Re: Difference between two tables

Ireneliu — Thu, 27 Jun 2019 04:13:44 GMT

Sorry for late reply.

I tried to change the relationships.

However, when I choose Retirement pension to make sure the difference value is right or not,
finding out the value of Actural results of Salary is included.

Is it because of there is no relationship about AccountName between table A and B?

Re: Difference between two tables

Ireneliu — Fri, 28 Jun 2019 01:46:20 GMT

I create a new table for Account Name and make 1:many relationships from Account Name to tables A and B.

Finally,getting the right value.