Forum Discussion

ckmalice's avatar
ckmalice
New Member
2 years ago

Compare 2 columns in 2 layer dimensions

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_IDDimA_ID
B001DA001


Dim_B

Batch_IDDimB_ID
B001DB001
B001DB002
B001DB003
B001DB004

 

Fact_A

Batch_IDDimA_IDFig1
B001DA0011000

 

Fact_B

Batch_IDDimB_IDFig2
B001DB001250
B001DB002250
B001DB003100
B001DB004400

 

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?

2 Replies

    • ckmalice's avatar
      ckmalice
      New Member

      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