Slicer displays all dimension values instead of filtering by related fact table in DirectLake mode

[amaaiia]

Hi,

I have a dimension table that contains information on all existing materials and a fact table that is related by the material ID, with a one-to-many relationship from the dimension table to the fact table. In a slicer visualization, I’m using a field from the dimension table, but it’s showing all possible values without filtering for those that actually exist in the fact table. For example, if I should see 10 materials in the filter, I’m seeing 40 instead. My semantic model is in Direct Lake mode, so I’m unable to create calculated tables or columns in the semantic model.

Any ideas?

[rubayatyasmin · Accepted solution]

Hi, @amaaiia 

this is common for PBI. You can use visual level filter in that slicer. 

Create a measure, example, 

FilteredMaterials =

IF(

    COUNTROWS(RELATEDTABLE('YourFactTableName)) > 0,

    1,

    0

)

use this measure in the visual level filter for the slicer. set to 1. It will work. 

[amaaiia]

Hi,

This approach works when a dimension is filtered. The point is that I need the values in slicer visualization to show dimension values only for values that already exist in fact table, but before filtering. That is, once I open the report, before doing any fltering, I need slicer to show only existing values.

[rubayatyasmin]

The measure is applied in a slicer visual. Get me a demo, I will update the pbix file so you can see the results. 

[amaaiia]

Finally working. Thanks!

[rubayatyasmin]

Happy to help

[AlexFernandes]

Hello. Can you please share how you made it work before selecting a filter?

[rubayatyasmin]

Did you create the measure that I have suggested? I create a slicer visual using that measure. 

Thanks 

[AlexFernandes]

Yes. The fields available in the dimension are correct (only the ones who exist in the fact table), but the results that appear in other visuals are related to everything in the dimension.
For example, if i have a table Product (id, name, stock) and Sales (productid, ...); and i use a slicer with Product(name) and apply that measure, the result of SUM(stock) still returns the sum of all the stock. What i wanted was to have the sum of only the products with sales

[rubayatyasmin]

Don't you have a relationship with the product and sales table? Normally the sales table should have a itemId or ProdID whatever the id name to connect to the product table to get the product name. This should return the desired result. If not then try to merge product table with sales table and keep the stock column in the sales table. This should help