Do's and Don't with cascading slicers in Power BI

Trying to get your slicers to filter right in Power BI? Patrick looks at some do's and don'ts with cascading slicers to get you in the right spot.
📢 Become a member: guyinacu.be/membership
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/courses
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com/tools/
#PowerBI #Slicers #GuyInACube

[@jonathanjedrczak1753]

Thanks for the great video! I'd seen/read most of the other content you referenced, but not fully understood it (in particular the Alberto solution). This put all the pieces together in a simple way

[@juansepowerplatform]

Great video Patrick, consolidated all the methods into one video. Thank you

[@mistertkn21]

and Guy in a Cube has the answer to my question once again! Thanks, Patrick

[@avatorl_in_a_hammock]

Thank you for the video Patrick! It's great as always.

[@toma4528]

Thanks a lot Patrick. This is all I needed. Didnt know I could be able to specify all fact tables in one measure. ✌️✌️

[@laurentmarc1928]

Excellent Patrick ! I love "The Alberto" :-) Thx a lot

[@bettina.e-k]

Thank you again, Patrick, for such a clearly explained video demonstrating the importance of data schema in visual interactions & UX. Not to mention TOP TIP with regard to measure-based Fact Table filtering: I had been using COUNTROWS(), but the solution from Alberto is much more elegant!

[@ketanpatil3489]

fantastic video Patrick...excellent tips... I will love to try out Alberto approach...

[@billsomers5775]

Brilliant! Worth watching multiple times.

[@nicob5477]

Thanks for this video! It’s exactly what I was currently wondering “how do I keep cascading slicers in a star schema”

[@thenerdyguy8388]

I love the video on such a tricky topic :), thanks guys

[@mikloshimics7789]

In option 2 when going across multiple measures (fact tables) you can even use Alberto’s method. Create a new measure that is the sum of Measure1, Measure2 … And apply the not blank filtering on the sum of measures. It won’t be the most optimal as it is pointed out but it is easy to apply.

[@swathinageswaran9788]

Thank you sir. This was really healpful

[@RajaVelforyou]

Great video - thanks for sharing it 😇

[@mikepelli]

Great content!! Thanks!

[@mukuldevbisht2672]

Greaaaat! Awesome video🔥

[@antonycatella5901]

I have always used 'The Alberto' so simple to implement with multiple fact tables

[@sahiladya8473]

Awesome. If we need to sync multiple slicers in different tables in model, we have to create a calcualted column which is concatenate of multiple columns which we need to filter and cascade. This result in many to many relationship cardinality between multiple tables on which the that relationship column is created. Is there Any way we can change its cardinality or activate relationship on multiple columns at one time?

[@gahhef]

Patrick, great video as always. Alberto's method doesn't seem to work for a date slicer using a range (i.e. between, after, before). Is there a known solution?

[@AJM2183]

Nice, love the Alberto and Snowflake

[@johnhebenstreit1274]

Is this applicable for an organizational chart use case? i.e. display corresponding sales data by the person selected n the organizational chart?

[@NirRobinson]

Great summarisations of this topic!!! Is the pbix file Is available for downloading? 🙏

[@analisecritica8722]

Hello, how to select only one option in two different filters? example: In Filttro 1 it has the options (A, B ,C) and in Filttro 2 it has the options (D,E,F). So, how to select only 1 option among all options (A,B,C,D,E,F)?

[@emrc9777]

Great Video! Question: I commonly see/hear people saying to avoid bidirectional relationships because they cause “all kinds of problems”… but rarely does anyone get into examples of what those problems are (real examples vs. academic dissertations) …. Is it possible that the data models created by us “mere mortals” just dont get to the complexity where bidirectional relationships would cause problems? Thanks!

[@travelicious7100]

Hi Patrick, In a date slicer(before), Is it possible to put minimum date as maximum date -5? That is five days less than the selected date. Please let me know if there is any solution for this

[@mohitdhand5094]

Hi Patrick, when computing growth for a sales person, I want to compare it with designated region/city/country. Is it possible?

[@lopypop]

Great video Patrick! I think it would be helpful if you include some decision criteria for when to use which option. For example, I can get away with using bi-directional filtering on smaller datasets without any noticeable front-end issues. Similarly, some flat tables may just be too big when you have 10+ columns and millions of rows (in which case I'd ask if the filters should be chained in the first place)

[@Fernando_Calero]

Hi @Patrick, this solution doesn't work if instead of using a slicer we use a chart to filter the measure, right?

[@TruthASAP]

Do you have a video about doing the hierarchys you mentioned: "I have them hidden because I created a hierarchy"?

[@ajaaskelainen]

Awesome 👏

[@PedroCabraldaCamara]

veeeeeeeery gooood!!!

[@abubakrsaadmakyhoun7535]

We follow Alberto method in out projects :)

[@tarun007]

Patrick, if we use bi directional in a one (dimension table) to many(fact table) scenario where the dimension table's columns are distinct. It doesnt throw any unexpected result and works perfectly fine. Its a different case, if the dimension table does consist of duplicate value, you are right to point that it displays unexpected result at times.

[@Paul-tm6lw]

This! Also create composite keys if necessary

[@dindoasencio9254]

Hey Patrick! Great content as always. thanks! how about the visual interaction where you can set to filter visuals/slicers based on another visual state?

[@jonathanjedrczak1753]

I spent a while trying to do this the other day but from what I found it's not currently possible :(

[@hukumka2601]

Visual interactions do not affect the filtering directions in the data model. If the model does not let one table to filter another one, so the slicer based on the first table would not be able to filter values from another table, even if "visual interactions" does not forbid it.

[@phlppnsk]

I am basically doing "The Alberto" in all my reports, but I am using the COUNTROWS() function for it. Would the ISEMPTY() one be better from a performance point of view?

[@dariuszspiewak5624]

Yes it's better. It's optimized similarly to the EXISTS operator in SQL. COUNTROWS needs to scan a table to count rows. ISEMPTY returns a value as soon as it's known, which means that if it finds a row in the current context, it does not have to scan any further.

[@mihir_thakkar4305]

Hello Patrick,, Can you please help me how to write the measure for the following: 1) I have a date slicer and it's in the slider form where I have taken all consecutive dates from the date table. 2) I have a Card visual where I want to Display the date which is selected in the date slicer. I'm not able to achieve this display. for eg, if date slicer selects 01/05/2020 upto 30/05/2020 Then we want the display on card as May 1, 2020 - May 30, 2020. This is required because there is a hidden filter pane and the user needs to have a visual of the dates selected so that it can help them in analysis. Kindly help me with a measure to achieve the above. Thanks.

[@MasterCamus]

Why woupd be bad to flat customer amd geography tables?

[@reazurrahman3515]

Nice👍

[@HariharasudanP]

@patrick - by doing Alberto method, does it impact performance by any chance ?

[@ketanpatil3489]

I have gone through vlogs of Alberto and Marco @sqlbi channel...those guys really know efficient techniques to model the data with optimum performance

[@smartdata678]

Alberto rules! :)

[@ProtValnus]

Just discovered your channel and love it. Real question tho. In your example. If nothing is selected for any of the slicers. How do you get the visuals to no display anything ?

[@GuyInACube]

Great question. Stay tuned for the video!!!!!!

[@ProtValnus]

@@GuyInACube Tuned :)

[@thomasivarsson2468]

If you have a SQL source you can cretate dimensions that only have a related fact record with an inner join. What you will miss is what has not happened. Products not being sold and so on. Great video.

[@ynwtint]

Mann!! really THE ALBERTO .. is the measure name..

[@Miketochy]

1:54 Yeah so “Alberto…has done something on this before, but see, I showed you guys a workaround long before he did” 🎤

[@chengwang411]

Your video editing skills is better than Alberto’s 😆

[@hakrajput]

Hi Abhishek, i am doing some of my office related work on power Bi, i need your support i have a table where i am applying filter it is given me the value , i want remaining unfiltered value to in same table Like Jamie have share of 59.9% , i want remaining share contribution too in the same table.

[@nigelreed9330]

Great video - and now trying "The Alberto" One issue with the slicer is that if I have a "Select All" option on ta drop down slicer, "The Alberto" seemes to be ignored in a Matrix Visual that is using the slicer as a column in the matrix. All ie the values are in the matrix even though the slicer is filtered Is there a work around for this? I'll keeo hunting foe a solution too Thanks for the video again.