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,

 

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.