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

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: