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,

 

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

      • nooneliveforeva's avatar
        nooneliveforeva
        Frequent Visitor
        9 years ago

        Hi sumit4732,

         

        Done that, but this create a "Solution" table with many column,

        when i drag a location column to the dashboard, it doesn't contain any data :(