cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 location is only in 2017 :

Location Date

 LONDON 2017

1 ACCEPTED SOLUTION
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
14 REPLIES 14

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

Frequent Visitor

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

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

Frequent Visitor

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 😞

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

Frequent Visitor

Hi @sumit4732 :

There is no relationship

Please share screenshot of table view for Solution table

-Sumit

Frequent Visitor

Hi @sumit4732,

Edited for privacy but this is it :

Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

@v-jiascu-msft

Very nice thank you all for your help

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

Frequent Visitor

Hi,

I want location where date is only in 2017.

`CALCULATETABLE(table, table[date]=2017)`

This will give me :

 PARIS 2017 LONDON 2017

I only want :

 LONDON 2017

London where not present in 2016

Resolver II

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.

Frequent Visitor

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

i want PRESENT IN 2017 AND NOT PRESENT IN 2016

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors