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
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!
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.
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)
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.
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?
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?
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.
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!
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?
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?
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.
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
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 ?
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)?
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.
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.
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.
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.
What if you have two data tables; All Sites and Pages. You want a slicer to select a site and then a table or another slicer to show the pages in that selected site (One to Many relationship). If you select a site, then a page, but then select a different site from the previous slicer, that page is still selected and shows up on every site until another page is selected (the invalid page disappears). I thought a calculated table might do the trick by filtering the Pages data: FilteredPages = FILTER(Pages,Pages[SiteUrl] = SELECTEDVALUE('All Sites'[SiteUrl])) But, nope! Nothing shows up in the visual for the PageUrl value in the FilteredPages table. So that seemed to be a bust. Anything selected in the Pages table "sticks" until unselected. Nothing seems to clear the selections of the Page visual when the Sites value is changed. Thoughts?
![BLACK BAG - Official Trailer [HD] - Only in Theaters March 14](http://i.ytimg.com/vi/Du0Xp8WX_7I/mqdefault.jpg)
and Guy in a Cube has the answer to my question once again! Thanks, Patrick
Great video Patrick, consolidated all the methods into one video. Thank you
Brilliant! Worth watching multiple times.
Thanks a lot Patrick. This is all I needed. Didnt know I could be able to specify all fact tables in one measure. ✌️✌️
Thank you sir. This was really healpful
Thank you for the video Patrick! It's great as always.
Excellent Patrick ! I love "The Alberto" :-) Thx a lot
Don't know if I would still have a job if it wasn't for Guy in a Cube! THANK YOU AGAIN
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
fantastic video Patrick...excellent tips... I will love to try out Alberto approach...
I love the video on such a tricky topic :), thanks guys
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!
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.
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)
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.
This! Also create composite keys if necessary
Thanks for this video!
It’s exactly what I was currently wondering “how do I keep cascading slicers in a star schema”
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?
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?
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.
I have always used 'The Alberto' so simple to implement with multiple fact tables
Great video - thanks for sharing it 😇
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!
Do you have a video about doing the hierarchys you mentioned: "I have them hidden because I created a hierarchy"?
Great summarisations of this topic!!!
Is the pbix file
Is available for downloading?
🙏
Great content!! Thanks!
Nice, love the Alberto and Snowflake
Hi @Patrick, this solution doesn't work if instead of using a slicer we use a chart to filter the measure, right?
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?
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?
I spent a while trying to do this the other day but from what I found it's not currently possible :(
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.
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
We follow Alberto method in out projects :)
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 ?
Great question. Stay tuned for the video!!!!!!
@@GuyInACube Tuned :)
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)?
Is this applicable for an organizational chart use case? i.e. display corresponding sales data by the person selected n the organizational chart?
Greaaaat! Awesome video🔥
Awesome 👏
Hi Patrick, when computing growth for a sales person, I want to compare it with designated region/city/country. Is it possible?
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.
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.
veeeeeeeery gooood!!!
@patrick - by doing Alberto method, does it impact performance by any chance ?
I have gone through vlogs of Alberto and Marco @sqlbi channel...those guys really know efficient techniques to model the data with optimum performance
Nice👍
Alberto rules! :)
Your video editing skills is better than Alberto’s 😆
1:54
Yeah so “Alberto…has done something on this before, but see, I showed you guys a workaround long before he did”
🎤
Why woupd be bad to flat customer amd geography tables?
Mann!! really THE ALBERTO .. is the measure name..
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.
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.
What if you have two data tables; All Sites and Pages. You want a slicer to select a site and then a table or another slicer to show the pages in that selected site (One to Many relationship). If you select a site, then a page, but then select a different site from the previous slicer, that page is still selected and shows up on every site until another page is selected (the invalid page disappears).
I thought a calculated table might do the trick by filtering the Pages data:
FilteredPages = FILTER(Pages,Pages[SiteUrl] = SELECTEDVALUE('All Sites'[SiteUrl]))
But, nope! Nothing shows up in the visual for the PageUrl value in the FilteredPages table. So that seemed to be a bust.
Anything selected in the Pages table "sticks" until unselected. Nothing seems to clear the selections of the Page visual when the Sites value is changed. Thoughts?