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
Icon for Advocate II rankAdvocate II
9 years ago

Hi nooneliveforeva,

Do you see data in solution table, please make sure that there is no relationship created automatically by PowerBI which is filtering the data.
Also please share screenshot and sample data for clear understanding out problem.

 

-Sumit

  • sumit4732's avatar
    sumit4732
    Icon for Advocate II rankAdvocate II
    9 years ago

    Hi nooneliveforeva,

     

    Please share screenshot of table view for Solution table

     

    -Sumit

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        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