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.

     

     

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