Forum Discussion
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_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?
2 Replies
- AnonymousNot applicable
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
- ckmaliceNew 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