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

sumit4732's avatar
sumit4732
Advocate II
9 years ago

Hi nooneliveforeva,

 

Can you be please eloborate more on what is the issue that you are facing?

 

from your post what I can get is that you want subset of the first table where date is in 2017.

If thats the case you can use CALCULATETABLE for New table  

Click on New table in Modeling tab and use below formula

CALCULATETABLE(table, table[date]=2017)

 

Hope this helps.

-Sumit  

  • 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.

      • nooneliveforeva's avatar
        nooneliveforeva
        Frequent Visitor
        9 years ago

        Thank you for your reply,

        using a slicer give me result where Location is present on 2017, but 

        i want PRESENT IN 2017 AND NOT PRESENT IN 2016