Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
nooneliveforeva
Frequent Visitor

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

1 ACCEPTED SOLUTION

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 )
                )
        )
)

DAX Filter not in date.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

14 REPLIES 14
sumit4732
Advocate II
Advocate II

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]))

 Step1Step1

2. Create a custom column in main table 

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

 Step2Step2

3. Now Create the required table using the custom column

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

 Step3Step3

This will give you the desire results 

 

-Sumit 

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.

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

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 😞

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

Hi @sumit4732 :

Bi.png

 

There is no relationship

Hi @nooneliveforeva,

 

Please share screenshot of table view for Solution table

 

-Sumit

Hi @sumit4732,

Edited for privacy but this is it :

BI2.png

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 )
                )
        )
)

DAX Filter not in date.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

@v-jiascu-msft

Very nice thank you all for your help

sumit4732
Advocate II
Advocate II

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  

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

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.

Thank you for your reply,

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

i want PRESENT IN 2017 AND NOT PRESENT IN 2016

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.