Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
My problem:
I want my report to be filtered by "FD1 = X" OR by "FD2 = X", FD1 and FD2 being 2 different variables.
The report has to show data from individuals having X value either in the FD1 variable OR the FD2 variable.
If I place 2 filters (one for FD1 and one for FD2) on my page I will filter individuals having X value in the FD1 variable AND FD2 variable.
Sorry if I'm not understandable, my English is quite poor.
Thanks in advance
Solved! Go to Solution.
That makes sense. Yet, I was hoping something that needed fewer manipulations (adding each filter individually is a bit repetitive) but I guess I can do it.
The other solution I found is to create a new column by concatenating FD1 and FD2. Then I added an advanced filter on the page and that requires the concatenated variable to contains "X".
@Maxfran If I'm understanding your post correctly, you want to filter FD1 or FD2 for different 'X' values that you want to be able to change dynamically?
First you will need an unrelated filter table, so add a new table to the data model that contains all values of X that you would want to filter for:
UPDATED based on @Maxfran 's questions :
To create it in Power Query Editor:
In the Transform Data Power Query Editor you can duplicate the base query (right click on it in the Queries pane). Rename this duplicated query to 'Filter'. Then select the FD1 column and convert to list (find the button in the Transform tab in the ribbon). Remove duplicates so this only has each value once. Convert the List back to table (List Transform tab in ribbon).
Set the data type and name this column and close and apply changes.
I called the table Filter and the column X, so we will have 'Filter'[X] to reference this column in DAX.
Then you will need to create a new calculated MEASURE similar to:
@nandukrishnavs 's solution will work if 'X' is static and you don't need the slicer on the report.
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
https://sites.google.com/site/allisonkennedycv
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Btw, since I'm a newcomer to Power Bi, I'm not sure about how to deal with "First you will need an unrelated filter table, so add a new table to the data model that contains all values of X that you would want to filter for (you can do this in the Query Editor by duplicating a column of FD1 or FD2). Remove duplicates so this only has each value once. I called the table Filter and the column X, so we will have 'Filter'[X] to reference this column in DAX."
I duplicated the column "Première fédération déclarée" (FD1) in base and name it "Filtre", then I removed the duplicates. I then created a new table named "Filtre" by doing this:
@Maxfran I prefer to create the new tables in Power Query Editor rather than DAX if possible just for performance purposes, but if what you did is working that is fine too.
To create it in Power Query Editor:
In the Transform Data Power Query Editor you can duplicate the base query (right click on it in the Queries pane). Then select the FD1 column and convert to list (find the button in the ribbon). Set the data type for this list and close and apply changes.
Then your measure in report should be fine, but yes you are right it cannot be used in page visual since it's a measure, so you will need to add it to each visual level filter on the page.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thanks for your reply. I wanted to use it one the whole page so a measure won't work as I understand...
I'll try to figure something else 🤔
@Maxfran The measure can work on whole page, just requires a bit more setup because you'll have to add it as a filter to each visual. Once you've done that though, the new column for X can be added as a page level filter or as a slicer, and that will change the value of all the measures that you previously added as filters to the visuals, and thereby will update everything on the page. Voila!
Does that make sense?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
That makes sense. Yet, I was hoping something that needed fewer manipulations (adding each filter individually is a bit repetitive) but I guess I can do it.
The other solution I found is to create a new column by concatenating FD1 and FD2. Then I added an advanced filter on the page and that requires the concatenated variable to contains "X".
@Maxfran Concatanated column was my first thought, but not knowing how your data is structured I wasn't sure what that would pull through that you don't want. For example:
If X=70 and FD1=707 and FD2=90, the concatanated column might look something like 707-90, it both would CONTAIN 70 even thought neither column was EQUAL to 70. Since you specified = I stayed away from that option, but if that's not an issue for your data or if you can get the format of X and the concatanated column right so it's not a problem, then you're right it will be much less laborious than my solution. 🙂
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy wrote:@Maxfran Concatanated column was my first thought, but not knowing how your data is structured I wasn't sure what that would pull through that you don't want. For example:
If X=70 and FD1=707 and FD2=90, the concatanated column might look something like 707-90, it both would CONTAIN 70 even thought neither column was EQUAL to 70. Since you specified = I stayed away from that option, but if that's not an issue for your data or if you can get the format of X and the concatanated column right so it's not a problem, then you're right it will be much less laborious than my solution. 🙂
Hi,
Thanks for your reply. As you explain, this solution is viable for my type of data.
I concatenated FD1 and FD2 in a new variable with the syntax: FD1_FD2_ (with underscores). Then when I use this variable as a filter, I select "contains" and state the modality "X_" (with an underscore).
Using underscores allows me to avoid the fact that X could be contained in another modality (as you explained, e.g. 70 is in 707).
@Maxfran You know your data better than me, so I hopefully you're right, but your reasoning is not fully developed, so I am posting this for others and in case your data is not consistent. Underscore will help some, but not in the example I gave, as 707_707 will still CONTAIN 70. So basically you need to carefully consider your values for X and make sure that they none of the possible X values can ever contain another.
For example, setting X value to 7, 7 would be contained in 7, 70 and 700, but you really only want the items that are equal to 7 (so not the 70 and 700).
Is X a number or text? If it's number you could get around this problem by setting the X to text and forcing it to always be exactly the same number of digits.
7 would become 007
70 would become 070
700 would stay 700
Then setting your X value to 007 will never be contained in 070 or 700.
Does that make sense?
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
https://sites.google.com/site/allisonkennedycv
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thanks for this precision.
It 707 contains 70, but not 70_ with an underscore implemented during the concatenation.
So, if I filter to have only "70_", it will be fine.
Actually, my data are text but I had the same issue that you presented with numbers.
@Maxfran of course, sorry I didn't read carefully enough. Genius solution.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy thanks for your reply
Unfortunately, it does not work.
I created a table named "Filtre" and the column is also named "Filtre". Then I create a measure:
Create a calculated column/ custom column to identify if the row needs to filter or not filter.
Example
FilterCondition =
IF (
table[FD1] = X
|| table[FD2] = X,
"Filter",
"Not Filter"
)
Then apply this newly created calculated column in page level filter.
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Hi @nandukrishnavs , thanks for your fast reply!
The fact is that I need to be able to change the filter. First I can do it with X but then Y, then Z...
If I need to create a column every time, that's not really effective...
Btw,
I find this helpful topic https://community.powerbi.com/t5/Desktop/Or-Condition-on-filter/m-p/833445#M400287
But it's not working on filter on the page. @v-lid-msft
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |