Different behaviour with Table and Slicer Visuals

[D4imi0n]

Hello all,

I currently facing a Power BI behavior which I can't quite explain. Maybe the solution is simple but not quite tangible for me.

I have the following model. Very simple:

In addition, I have the following page filter:
- AB_NAME from Table D_VERMITTLER_CONFORMED

- ID_ZEIT_MONAT from Table D_ZEIT_MONAT

What is working fine:

If I place a table visualization on the page and take columns from D_ZEIT_MONAT and D_VERMITTLER_CONFORMED the page filters are drawn correctly. The SQL looks good because Power BI understands that it should join over three tables.

Challenge:

If I place a slicer visualization on the page and take one column from D_VERMITTLER_CONFORMED the page filters are NOT drawn correctly. It seems that there is a problem with the ID_ZEIT_MONAT filter.

I could trace that there is a probleme with the ID_ZEIT_FILTER connection because for a test I added the field ID_ZEIT_MONAT to the slicer visualization. With this I then got the following failure:

You are using fields that don't have a supported set of relationships

My specific question is if I am doing something wrong. Perhaps it should be said that the model runs in Direct Query Mode.

Thank you all for your help and time.

[selimovd]

Hallo @D4imi0n ,

I checked your file, the problem is the relationship.

In the right table you can change it to a slicer because they have a direct connection. So you can get all the years from the Date table and then filter Sales and get the related countries:

The left table you can get all the years from the Date table, but then the relationship to the Geography table cannot be made. The connection would go to the Sales table and then from the Sales table to the Geography table, what is not possible because the filter direction is not allowing that:

In general this is not a common way to create a hierarchy slicer. I would recommend to just add 2 slicers, one for the year and one for the Geography. 

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[v-lionel-msft]

Hi @D4imi0n ,

Would you mind providing a sample .pbix file?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[D4imi0n]

Hi Chen, 

thanks for the reply. I try to append a sample pbix. 

Cheers Jeffrey

[D4imi0n]

Hi Chen,

sry for the delay. I was able to rebuild the problem with a import model.

Power BI Sample File 

If you start the sample you will see two tables. 

Please change the tables to Slicers to see the problem. 

Hopefully you can download the sample.

Thanks

[PaulDBrown]

@D4imi0n 

You cannot create hierarchies from tables which are not directly related. A solution to including the Geography Table is to place two slicers in the page: one for year and one for country (from your geography table.

Here is an example using a sample dataset. First the model:

To avoid having to make the direction of the relationships between the fact table and the dim tables "Both",  create two measures to filter the other slicer based on what is selected in the slicer:

1) To filter the year based on the selection made in the country slicer:

Filtered Year =
VAR FilteredYears =
    CALCULATETABLE ( VALUES ( 'Dim Year'[Year] ), RELATEDTABLE ( FactTable ) )
RETURN
    COUNTROWS ( FilteredYears )

2) To filter the country based on the year selected:

Filtered Country =
VAR FilteredCOuntries =
    CALCULATETABLE ( VALUES ( 'Dim Country'[Country] ), RELATEDTABLE ( FactTable ) )
RETURN
    COUNTROWS ( FilteredCOuntries )

Add each measures to the "filters on this visual" in the filter pane to the corresponding slicer:

and you will get this:

[D4imi0n]

Hi everyone, 

thank you all for your feedback. 

@selimovdThanks for the clarification regarding joins.

@PaulDBrown Thank you for this fantastic solution. I will try this now. 

I must apologize if I have asked my question incorrectly. In my current model in Direct Query Mode I have the following model:

D_SELLER and F_CONTRACT are big tables!

With this modeling I get the desired data in a suitable time if I put a month und sellers filter on the whole page. 
The reason for this is that I need to hit the optimizer on the database and this only works if I restrict the month.

With this in mind, I just wanted to place the sellers (AB_NAME) into a slicer visual on the page (and not in the page filter) to give the user of the report the ability to filter a seller. 

From then on I had performance problems because the monthly filter from the site did not affect the seller slicer whereby all sellers are considered over all monthly slices. 

Is there a possibility to tell the Sellers-Slicer (AB_NAME) to take the Month page filter into account? 

Thanks for your help!

[PaulDBrown · Accepted solution]

@D4imi0n 

Just out of curiosity, is there a reason you don't have a relationship between the Date Table and the D_seller table and have a separate Dimension table for D_Seller? (to avoid a potential relationship conflict). Something like this:

[D4imi0n]

@PaulDBrown Thank you for the idea. Sadly this was not the solution. 
I have discussed the issue with Microsoft. They are now looking into it.

Thanks!

[D4imi0n]

Hi @PaulDBrown ,

I will try this one first. 

Thanks