Forum Discussion
Helper IIIUnfiltered results
Hi,
I need some help on the DAX below i try to create to filter the values for each country, but it just subtracts the total figure for all the countries and doesn't filter for each country separately as well.
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]),
FILTER( TableB, (TableB[Country] = "England" && TableB[LType] = "FL Type 2")
||
(TableB[Country] = "Wales" && TableB[LType] = "FL Type 2") ) )
RETURN FilteredB - FilteredA
Example:- If i use Country from the Table B in the rows field of the matrix visual and i create the above measure (Measure_Subtraction ) in the Table A, it gives me the results as below highlighted in red, but i am looking for the results as
showing highlighted in green below.
| VAR FilteredB | VAR FilteredA | FilteredB-FilteredA (Wrong Results | FilteredB-FilteredA (Expected Right Results) | |
| England | 10 | 2 | 10 | 8 |
| Wales | 5 | 4 | 5 | 1 |
| Total | 15 | 6 | 9 | 9 |
Hope i could explain it clearly and thanks in advance for any help.
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
16 Replies
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.
samioberoihttps://drive.google.com/file/d/1XC2C8EU-9T_tax6BWRZ_8soY5pLmmtBS/view?usp=drive_link
Hi,
Thanks for your reply and sorry for me being late to respond. I am adding this sample Power BI file link here. I don't know if you will be open it, or if you can let me know please how to upload the file on here as doing it first time here i am finding it a bit difficult.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.10TIA
- samioberoi
Hi,
Thanks for your effort. It worked fine.
Thanks again.
- Ashish_Mathur
Super User
Hi,
Share the download link of the PBI file. Show the problem there clearly.
- lbendlin
What's the purpose of the bridge table? Can you use proper dimension tables instead?
- samioberoi
Hi 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.
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
Hi, samioberoi
I wish you all the best. Previously we have provided a solution to help you solve the problem. Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.Best Regards
Jianpeng Li
