Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to Solution.
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 - FilteredA
Here 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.
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
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 - FilteredA
Here 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.
Hi,
Thanks for your effort. It worked fine.
Thanks again.
https://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.10
TIA
What's the purpose of the bridge table? Can you use proper dimension tables instead?
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 A
Country | 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
Hi Ibendlin,
Thanks for your reply. It has worked great.
Regards
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.
Hi, @samioberoi
You can upload the PBIX file without private data to Github and post the link here so that we can easily get it.
Finally, you should show your expected results based on your sample data.
Best Regards
Jianpeng Li
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
Use a common dimension table.
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?
create one if there isn't one.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Hi Ibendlin,
Sorry for the late response. Please find the sample file link here. Don't know if it will open at your end, or please let me know how i can upload the PBI sample file as i struggled to find out an option to upload it here.
Expected results for the measure called "Subtraction_measure" should be like:
For Wales --- 1573+104.56 = 1677.56
-67-1677.56 = -1744.56
For England ---- 52.52 - 350.62 = -298.10
.https://drive.google.com/file/d/1XC2C8EU-9T_tax6BWRZ_8soY5pLmmtBS/view?usp=drive_link
Thank you
Hi,
Share the download link of the PBI file. Show the problem there clearly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |