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

PARIS2016
PARIS2017
LONDON2017
MADRID2016
NEW-YORK2016

 

I want to have a table where location is only in 2017 :

 

Location Date

LONDON2017

 

Thank you for your help

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

14 Replies

  • sumit4732's avatar
    sumit4732
    Advocate II
    9 years ago

    Hi nooneliveforeva,

     

    I dont have a direct solution to it, but a workaround.

    Steps:

    1. Create on temp table using SUMMARIZE

        temp = SUMMARIZE(Issue,Issue[location],"countofyear",DISTINCTCOUNT(Issue[year]))

     Step1

    2. Create a custom column in main table 

       countofyear = LOOKUPVALUE(temp[countofyear],temp[location],Issue[location])

     Step2

    3. Now Create the required table using the custom column

       Solution = CALCULATETABLE(Issue,FILTER(Issue,AND(Issue[year]="2017",Issue[countofyear]=1)))

     Step3

    This will give you the desire results 

     

    -Sumit 

    • nooneliveforeva's avatar
      nooneliveforeva
      Frequent Visitor
      9 years ago

      Hi  sumit4732,

       

      Doesn't work, step 1 give me :

      L'expression fait référence à plusieurs colonnes. Plusieurs colonnes ne peuvent pas être converties en une valeur scalaire.
      • sumit4732's avatar
        sumit4732
        Advocate II
        9 years ago

        Hi nooneliveforeva,

         

        It looks like you are trying to create a measure, step 1 is for creating a New Table : click on Modeling tab, and than New table 

        Please let me know if you face any issue with this, also please share shreentshot that will help.

         

        -Sumit

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