Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have 2 fact table and 1 dimension table. They are all linked via the dimension table. The dimension table has all policyid in both fact tables. Like so..
DimPolicy
- PolicyID
FactTb_1
- PolicyID
- Measure_A
- Measure_B
FactTb_2
- PolicyID
- Measure_A
- Measure_B,
I want to create calculated measures to compare and identify where measures do not match, or policies do not exist on both fact tables. Please let me know best way to do this.
My thinking was , I should create calculated measures on dim table to evaluate measures but I am quite new to power bi and not sure how to do this. Please help.
Desire Outcome
PolicyID, MatchingStatus
1001 Matching
1002 Missing FactTb_1
1003 Not Matching
1004 Missing FactTb_2
Solved! Go to Solution.
Hello @Tekfes
The table a measure lives on doesn't really matter so you can write them where they may the most sense. I will assume for the sake of this discussion that you have a column in both Fact tables called 'Premium'.
Write a measure on each one (again, doesnt matter really but it is easier to see what table you are summing if the measure is under the same table).
Fact1 Premium = SUM ( FactTb_1[Premium] )
Fact2 Premium = SUM ( FactTb_2[Premium] )
Then a thrid measure to do some compares using a SWITCH TRUE()
https://community.powerbi.com/t5/Community-Blog/SWITCH-True-Logic-In-Power-BI-What-s-Possible-With-T...
Compare = SWITCH ( TRUE(), ISBLANK ( FactTb_1[PolicyID] ), "Missing FactTb_1", ISBLANK ( FactTb_2[PolicyID] ), "Missing FactTb_2", [Fact1 Premium] <> [Fact2 Premium], "Not Matching",
[Fact1 Premium] = [Fact2 Premium], "Matching", "Other" )
Pull the policy ID in from your DimPolicy and add the compare measure. It will return the first TRUE result or "Other" if none of them evaluate true.
Hello @Tekfes
You already have the table you need, you can just add a calculated column to your DimPolicy table, we just need to change the measure a little.
Compare =
SWITCH (
TRUE (),
ISBLANK ( COUNTROWS ( RELATEDTABLE ( FactTb_1 ) ) ), "Missing FactTb_1",
ISBLANK ( COUNTROWS ( RELATEDTABLE ( FactTb_2 ) ) ), "Missing FactTb_2",
[Fact1 Premium] <> [Fact2 Premium], "Not Matching",
[Fact1 Premium] = [Fact2 Premium], "Matching",
"Other"
)
Hello @Tekfes
The table a measure lives on doesn't really matter so you can write them where they may the most sense. I will assume for the sake of this discussion that you have a column in both Fact tables called 'Premium'.
Write a measure on each one (again, doesnt matter really but it is easier to see what table you are summing if the measure is under the same table).
Fact1 Premium = SUM ( FactTb_1[Premium] )
Fact2 Premium = SUM ( FactTb_2[Premium] )
Then a thrid measure to do some compares using a SWITCH TRUE()
https://community.powerbi.com/t5/Community-Blog/SWITCH-True-Logic-In-Power-BI-What-s-Possible-With-T...
Compare = SWITCH ( TRUE(), ISBLANK ( FactTb_1[PolicyID] ), "Missing FactTb_1", ISBLANK ( FactTb_2[PolicyID] ), "Missing FactTb_2", [Fact1 Premium] <> [Fact2 Premium], "Not Matching",
[Fact1 Premium] = [Fact2 Premium], "Matching", "Other" )
Pull the policy ID in from your DimPolicy and add the compare measure. It will return the first TRUE result or "Other" if none of them evaluate true.
Thanks Jdbuchanan71.
While this works, I realised/think calculation measures will not get me what I eventually want to be able to do.
My end goal is to produce visuals based on “compare” column.
For example
Reconciliation Report No. Policies Premium Ant
Missing FactTb_1 10 123
Missing FactTb_2 12 300
Matching 10 450
Exception Report
List of PolicyId missing from FactTb_1
PolicyID 1
PolicyID 2
Do I need to think joining the fact tables and using custom column?
Many thanks,
Hello @Tekfes
You already have the table you need, you can just add a calculated column to your DimPolicy table, we just need to change the measure a little.
Compare =
SWITCH (
TRUE (),
ISBLANK ( COUNTROWS ( RELATEDTABLE ( FactTb_1 ) ) ), "Missing FactTb_1",
ISBLANK ( COUNTROWS ( RELATEDTABLE ( FactTb_2 ) ) ), "Missing FactTb_2",
[Fact1 Premium] <> [Fact2 Premium], "Not Matching",
[Fact1 Premium] = [Fact2 Premium], "Matching",
"Other"
)
Thanks you!