Compare 2 columns in 2 layer dimensions

[ckmalice]

Dear All,

Sorry that the question maybe asked by someone before but I cannot find out the solution

Currently I am going to create a dashboard table like this

 

For the column Reconciliation, it is a comparison of columns from 2 different tables, i.e. Fig1 - Fig2, if they are equal, green will be shown, or vice versa to show red.

For the relationship, it is constructed as below

1. Batch_MASTER --> Table_A --> Fact_A

2. Batch_MASTER --> Table_B --> Fact_B

BATCH_MASTER: BATCH_ID

Dim_A: BATCH_ID => DimA_ID

Dim_B: BATCH_ID => DimA_ID

FACT_A: BATCH_ID => DimA_ID => Fig1

FACT_B: BATCH_ID => DimB_ID => Fig2

For the column Reconciliation, it is a column added in Table_B with below formula

Reconciliation = 

Var fig1 = CALCULATE (SUM(FACT_A[Fig1]), ALLEXCEPT(BATCH_MASTER[Batch_ID]);
Var fig2 = CALCULATE (SUM(FACT_B[Fig2]), ALLEXCEPT(BATCH_MASTER[Batch_ID]);

Return

IF(fig1 = fig2, 1, 0)

Data:
Batch_Master

Batch_ID

B001

Dim_A

Batch_ID	DimA_ID
B001	DA001

Dim_B

Batch_ID	DimB_ID
B001	DB001
B001	DB002
B001	DB003
B001	DB004

Fact_A

Batch_ID	DimA_ID	Fig1
B001	DA001	1000

Fact_B

Batch_ID	DimB_ID	Fig2
B001	DB001	250
B001	DB002	250
B001	DB003	100
B001	DB004	400

As per the above table data, total for Fig1 and Fig2 should be 1000 and the green icon (1) is shown, however it showed red (0). When I check the value for the column for Fig1, I found it is returned 4000 instead of 1000

If I would like to have 1000, instead of creating a new summarize table for Batch_ID only, is it any other alternatives to achieve by using DAX?

[v-yiruan-msft]

Hi @ckmalice ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Reconciliation = 
Var fig1 =  SUM(FACT_A[Fig1] )
Var fig2 =  SUM(FACT_B[Fig2]) 
Return
 IF(fig1 = fig2, 1, 0)

Best Regards

[ckmalice]

Hi Rena

Thanks for the update, however it maybe another issue if I create as a measure in Batch Master

For the column Reconciliation, I need to build in Fact_B table since it is just one of the column to be used

In Fact_B table, I have another column as Overall will take the minimum value between the Column Reconciliation and Vaildate and Import column, if I create as an measure it will be another issue