What is the quickest way to exclude certain rows of products and their aggregated values from chart and card visuals but then have a way to quickly include these rows again when I need them to be included? So the idea is to be able to go back and forth between these two states.
Using the Filter Pane options and switching between DAX measures for one or two charts and cards is not a problem, but doing it to many might lead me to unknowingly miss a few.
I was wondering if there were any nifty ideas for doing this in an efficient and sure manner? The main thing is to make sure I don’t miss anything during all this back and forth between states.
If I understand the problem correctly, my inclination would be to build either a DAX calculated table or a virtual table variable containing the products to be excluded, and then use the EXCEPT table function to remove those rows from the full dataset dynamically within measures.
Here’s a forum post I responded to a while back utilizing a similar technique that you might find instructive:
Thanks, @BrianJ! The example is great but my scenario is not as intricate.
It’s just a question of how do I exclude Products x, y, z from one representation of the report and how to quickly include these same rows in another representation of the same report. Of course, it’s not just for one chart and card, but for many, which is why I need a way to do it quickly but ensuring I don’t miss one change.
It might be a question of doing 2 reports, but that seems a bit too much, given that the data is 90% the same?
There are probably a lot of different strategies to accomplish this, but here’s the one that strikes me as easily workable.
Assume you have a fact table called “Data”. Create a DAX calculated table called “DataXYZ” that includes all the rows associated with the products you want to remove from the analysis. Now write all your measures in such a way that instead of referencing Data, you reference EXCEPT(Data, DataXYZ). This will give you the analysis excluding all the Products X, Y, And Z.
Now to do the same report, but including those same products, you just need to make one change to the DAX calculated table filter condition, such that it returns an empty table with no data rows. Now this should return the same report, but this time INCLUDING products X, Y, and Z, since
EXCEPT(Data, DataXYZ)
will evaluate to be equal to Data if DataXYZ is an empty table.
You can do the same thing in Power Query, which might be even easier. For the DataXYZ table, you will just add a final step removing all the rows. To reverse the process, just delete that final step and rerun.
What would be the best way to do this for more than one table. For the products table it was an easy filtering of the product name that contained specific suffixes. The challenge is that not all the relevant tables have the product name/suffixes. A few related tables have codes, not names, so the Metric calculations in the report are still able to include these unwanted product values that come from tables that use codes, not product names.
So, I was wondering if there is a way to do this filtering of suffixed product names and their relevant codes, out of all numerous tables using M-code, so there’s no requirement to do anything manually nor the need for any DAX in the front-end?
In that case, I definitely think the best approach would be to write a custom M function that you could then apply to the relevant tables. Here’s a video from @Melissa that does a fantastic job explaining how to develop such a function.
If you have additional questions regarding how to implement this, I would recommend starting a new thread – that way we can also get contributions from experts and other members who typically only scan open topics.
