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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Maxfran
Helper I
Helper I

Or Condition on page filter

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

 

1 ACCEPTED 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". 

 

View solution in original post

14 REPLIES 14
AllisonKennedy
Super User
Super User

@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: 

ShowNoShow = IF(HASONEVALUE('Filter'[X]), COUNTROWS(FILTER(Table,Table[FD1]=SELECTEDVALUE('Filter'[X]) || Table[FD2]=SELECTEDVALUE('Filter'[X]))), 1)
 
Finally, add the [ShowNoShow] measure as a visual level filter to the visual(s) you want filtered and add a slicer to the report for [X].

 

@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


Please @mention me in your reply if you want a response.

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: 

Filtre = VALUES(base[Filtre])
 
Is that correct? 

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


Please @mention me in your reply if you want a response.

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?


Please @mention me in your reply if you want a response.

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


Please @mention me in your reply if you want a response.

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

 


Please @mention me in your reply if you want a response.

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. 


Please @mention me in your reply if you want a response.

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:

Fédération = IF(HASONEVALUE(Filtre[Filtre]);COUNTROWS(FILTER(base;base[Première fédération déclarée]=SELECTEDVALUE(Filtre[Filtre]) || base[Deuxième fédération déclarée]=SELECTEDVALUE(Filtre[Filtre]))); 1)
Where "Fédération" = the name of the measure, "base" is the principal dataframe where my data is stocked, "Première fédération déclarée" is the FD1 above, the first variable to look in, and "Deuxième fédération déclarée" is the FD2 above, the second variable to look in.
 
The measure is correctly created but it's impossible to drag & drop it in the "filter on this page" section. 
 
Any idea? 
nandukrishnavs
Super User
Super User

@Maxfran 

 

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
🙂


Regards,
Nandu Krishna

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.