Forum Discussion
Helper IIIUnfiltered results
Thank You lbendlin and Ashish_Mathur
Hi, samioberoi
I agree with Super User that you should make a dimension table, like your country column. First, I use the following M code to combine the country columns of the two tables and then deduplicate them to form a country dimension table:
let TableA1 = TableA[Country], TableB1 = TableB[Country], res = List.Distinct(List.Combine({TableA1,TableB1})), #"Converted to Table" = Table.FromList(res, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Country"}}) in #"Renamed Columns"Their relationship is as follows:
Then create a measure using the following expression:
Measure_Subtraction = VAR FilteredA = CALCULATE(SUM(TableA[Amount]),FILTER( TableA, (TableA[Country] = "England" && TableA[LType] = "Type 1") || (TableA[Country] = "Wales" && TableA[LType] = "Type 2") )) VAR FilteredB = CALCULATE(SUM(TableB[Amount]),'TableB'[LType] = "FL Type 2") RETURN FilteredB - FilteredAHere are the results:
I've provided the PBIX file used this time below.
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
see attached
Super UserWhat's the purpose of the bridge table? Can you use proper dimension tables instead?
samioberoiHi Ibendlin,
Table A and Table B without the Bridge table would create the Many-to-Many relationship. That is the reason i created the Bridge Table in between.
Thanks- lbendlin
Use a common dimension table.
- samioberoi
Hi,
Sorry for me being a naive for this. I don't think there is any common dimension table beween these two tables here. Or am i understanding anything wrongly?
Hi,
Please find the sample data with used measures and expected results here.________________________________________________________________________________________________________________
TABLE B
Country
Tax Plan
Amount
England
BLL Type 2
159.46
England
BMM Type 5
2077.18
England
GL Type 1
52.52
England
GL Type 2
31685.68
England
IFTD Type 2
92
England
QTEL Type 2
878.86
England
QTEL Type 5
458.54
Wales
GL Type 1
-67
Wales
GL Type 2
2127.62
Wales
QTEL Type 2
8264.19
Table ACountry
LType
Tax Plan
Amount
England
Type 1
GT Free Tax
-305
England
Type 2
GT Free Tax
350.62
England
Type 2
QT Free Tax
585.26
England
Type 5
QT Free Tax
137.56
England
Type 5
GT Free Tax
410.2
England
Type 5
QT Free Tax
2956.12
Wales
2013 boohoo
X Free G Tax
210
Wales
P-2013 boohoo
X Free G Tax
10
Wales
Type 2
QT Free Tax
104.56
Wales
Type 2
GT Free Tax
934.52
Wales
Type 2
QT Free Tax
1573
Measure used in Table B - Test = CALCULATE(
SUM('Table B'[Amount]),
FILTER(
'Table B',
'Table B'[Tax Plan] = "GL Type 1"
&& 'Table B'[Country] in {"England","Wales"}))
Final results measure in Table A - Subtraction_measure =
VAR A =
CALCULATE(
SUM('Table A'[Amount]),
FILTER(
'Table A',
('Table A'[Country] = "England"
&& 'Table A'[LType] = "Type 2"
&& 'Table A'[Tax Plan] = "GT Free Tax")
||
('Table A'[Country] = "Wales"
&& 'Table A'[LType] = "Type 2"
&& 'Table A'[Tax Plan] = "QT Free Tax")))
VAR B = [Test]
RETURN
B - A
Expected results looked for in the measure called "Subtraction_measure".
For Wales ---- 1573+104.56 = 1677.56
-67-1677.56 = -1744.56
England ---- 52.52 - 350.62 = -298.10______________________________________________________________________________________________________________
Apologies, i tried to load the PBI file link here, but it didn't work; so i had to used this method of giving you sample data and expected results. For any help on this, it will be much appreciated.
TIA
- lbendlin
see attached
- samioberoi
Hi Ibendlin,
Thank you for your effort in helping me. You measure seems to be giving the same results as it was giving before from the measure i created. I think there is an issue with the data somewhere. The problem is i can't give the actual data file; so, i will try to create a new dummy data file to try to match with the same error as i am getting on the actual file and will upload it here. I don't know if you could access the PBI file from my Google drive for which i tried to send you the link or not. If not, then please let me know how i can easily give you access to the dummy data PBI file, which i will try to create a bit later.
Thank you again.