Forum Discussion

nooneliveforeva's avatar
nooneliveforeva
Frequent Visitor
9 years ago
Solved

DAX Filter not in date

Hello,  I have issu using filter. This is what i have :   Location Date PARIS 2016 PARIS 2017 LONDON 2017 MADRID 2016 NEW-YORK 2016   I want to have a table where locatio...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi nooneliveforeva,

     

    Would you like to try this formula in a New Table.

     

    Result =
FILTER (
    'SourceTable',
    'SourceTable'[Year] = 2017
        && (
            NOT 'SourceTable'[City]
                IN CALCULATETABLE (
                    VALUES ( SourceTable[City] ),
                    FILTER ( 'SourceTable', 'SourceTable'[Year] = 2016 )
                )
        )
)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

nooneliveforeva's avatar
nooneliveforeva
Frequent Visitor
9 years ago

Hi, 

Thank you for your quick reply,

I want location where date is only in 2017.

CALCULATETABLE(table, table[date]=2017)

 This will give me :

PARIS2017
LONDON2017

 

I only want :

LONDON2017

 

London where not present in 2016

kaushikd's avatar
kaushikd
Resolver II
9 years ago

Put a slicer into the report area and select location.So when you click london the records with date 2017 and location London will be displayed.