Forum Discussion

samioberoi's avatar
samioberoi
Icon for Helper III rankHelper III
1 year ago
Solved

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 separate...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

  • samioberoi's avatar
    samioberoi
    Icon for Helper III rankHelper III
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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

  • samioberoi's avatar
    samioberoi
    Icon for Helper III rankHelper III
    1 year ago

    Hi Ibendlin,

    Thanks for your reply. It has worked great.

    Regards