I am struggling to find the best way to exclude slicer selection from data in reports. I have created a measure, however, the slicer selection selects the department of the company (for example, sales,finance, hr,etc) and the report shows performance of each department in compared to others. However, to calculate the attendance for example, the data comes from another table and that does not have the department column in it.
Also, I can’t really use a vlookup and add it since employees tends to have a high mobility rate. What would be the most beneficial and effective approach for such conditions?
@supergallagher25 Difficult to understand by just reading what you are trying to do exactly but if the idea is to show everything in the report except the selection over the slicer then you can use something like this: (need a disconnected table for this kind of report)
Sales Except Selected Brands =
IF (
ISFILTERED ( DisBrand[Brand] ),
VAR SelectedBrands =
ALLSELECTED ( DisBrand[Brand] )
VAR AllBrands =
ALL ( Products[Brand] )
VAR ExcludeSelectedBrand =
EXCEPT ( AllBrands, SelectedBrands )
VAR Result =
CALCULATE ( [Total Sales], KEEPFILTERS ( ExcludeSelectedBrand ) )
RETURN
Result,
--
-- Second part of the IF starts here
-- In case there is no selection then show the product with the highest sale
--
VAR AllBrands =
ALL ( Products[Brand] )
VAR ProductWithHighestSales =
MAXX ( AllBrands, [Total Sales] )
VAR WhichProductIsThat =
FILTER ( AllBrands, [Total Sales] = ProductWithHighestSales )
RETURN
-- CALCULATE ( [Total Sales], KEEPFILTERS ( Products[Brand] = "Contoso" ) )
CALCULATE ( [Total Sales], KEEPFILTERS ( Products[Brand] = WhichProductIsThat ) )
)
Hi @supergallagher25, did the response provided by @BrianJ and @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Thank you for the feedbacks, I had taken suggestion that “BI Elite”'s video and created it. However, it does not let me perform a select all here.
My report needs some modifications now, for example, I would need a filter for product line here and a separate filter for the product types. Users would be able to select all the product types from the product line or individual product types. I tried to apply the same methodology as what has been applied for product line. However, it didn’t seem to like it much. How would I approach such situation then? I am attaching copy of PowerBI Elite’s file here for example with the modification change here.
As you can see in the image above, it shows blank when all the product types are selected for a particular product line. I am trying to provide users with the option to select all product types for a particular product line or a few product types as per their needs. Exclude from Slicer Selection_2slicers.pbix (3.8 MB)
Antriksh I have also tried to do the same thing with your solution, but it leads to the same issue when it comes to two slicer options. Would you be able to suggest a different approach?
Please explain the expectation here. Initial requirement was to exclude selected value from Slicer in the Visual, solution for which is already provided.
If we are excluding the Product Line from Visual what with Product Type Filter do. Please explain in bit more detail for me to help.
Thanks
Ankit J
If requirement is different, please explain in bit more details.
Hi @supergallagher25, did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
I was able solve the issue numbers when select all is picked. However, when I click on select all on product type slicer, it shows all the products types from all product lines. However, I am currently trying to get it to only show the product types that are within that selected filter. It sounds a bit complicated so I will be adding pictures of what I am trying to get and what it is showing for further understanding.
When I have Personal Accessories selected from product line and select all option selected from product type, the graph on the left shows all the product types from all product lines as the image below.
However, Personal Accessories only has five products and the graph should be looking like this instead.
Thank you so much. This seems to be working pretty well. However, my only issue now would be when I click on select all, the graph on the right side shows blank instead of showing other product lines (that belong to other product types) data.
If requirements are still different, will suggest to explore yourself as by now you should have got idea of what to do. This is also the only way to learn DAX.
I did have it set up that way, with the implementation of the new measure it had stopped showing up others on the other visual which is why I had stated that it was happening to the graph on the right side in the file that I had attached above.
Anyways, thank you, I was able to fix the issue with a turnaround of the measure.
Hi @supergallagher25, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
