Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
samioberoi
Helper III
Helper III

Unfiltered 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

samioberoi_0-1735953803290.png

 

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 FilteredBVAR FilteredAFilteredB-FilteredA (Wrong ResultsFilteredB-FilteredA (Expected Right Results)
England102108
Wales5451
Total15699


Hope i could explain it clearly and t
hanks in advance for any help.


2 ACCEPTED SOLUTIONS
v-jianpeng-msft
Community Support
Community Support

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"

vjianpengmsft_0-1736149225263.png

Their relationship is as follows:

vjianpengmsft_1-1736151554582.png

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:

vjianpengmsft_2-1736151646844.png

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.

 

 

View solution in original post

lbendlin_0-1736796584153.png

see attached

 

 

 

View solution in original post

16 REPLIES 16
v-jianpeng-msft
Community Support
Community Support

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

 

v-jianpeng-msft
Community Support
Community Support

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"

vjianpengmsft_0-1736149225263.png

Their relationship is as follows:

vjianpengmsft_1-1736151554582.png

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:

vjianpengmsft_2-1736151646844.png

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

lbendlin
Super User
Super User

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

lbendlin_0-1736796584153.png

see attached

 

 

 

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

 

Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.  Show the problem there clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.