Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Thank you for your help
Solved! Go to 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 ) ) ) )
Best Regards!
Dale
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]))
2. Create a custom column in main table
countofyear = LOOKUPVALUE(temp[countofyear],temp[location],Issue[location])
3. Now Create the required table using the custom column
Solution = CALCULATETABLE(Issue,FILTER(Issue,AND(Issue[year]="2017",Issue[countofyear]=1)))
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 @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
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 :
PARIS | 2017 |
LONDON | 2017 |
I only want :
LONDON | 2017 |
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
85 | |
67 | |
49 |
User | Count |
---|---|
135 | |
112 | |
100 | |
66 | |
62 |