DAX Fridays! #44: CROSSFILTER

[@Enties]

You are the best PBI video maker. Thumbs up for you! I am really looking forward to seeing a the next video. Keep going. Please could you also sonetimes mention weather some function are available or not available in Excel Power Pivot? :)

[@CurbalEN]

+Martin Lučan Hi Martin! Thanks, really appreciate your comment and the fact that you like my work 😊! You are right, I will try to remember to mention if it is posible to do in excel, but if I forget, please make sure you remind me again! Perhaps +excelisfun can also help me with this :) Happy Friday!! /Ruth

[@cristinarueda5598]

To learn from you is so easy and immediately impactful in our everyday jobs, really love what you do. Thank you!!!!

[@CurbalEN]

+Cristina Rueda Wow Cristina, many thanks for your kind words! Really appreciate it :) /Ruth

[@Victor-ol1lo]

Excellent video Ruth ! Again an improtant brick to get a better understanding of DAX. Thumbs Up !!

[@CurbalEN]

+Victor Friesen Thanks!!! Really useful function for complex models :) /Ruth

[@nikhargesumit9069]

Thanks Ruth. Very Nicely explained.

[@excelisfun]

Thanks for the Cross Filter Fun : )

[@CurbalEN]

+ExcelIsFun Thanks to you for watching. Happy Friday :) /Ruth

[@learnspreadsheets]

Thanks Ruth! Love your videos, I think this could be very useful, but I'd like to learn more use cases (like you mention) or using none or one. Advanced relationships are also something I struggle with. Why two way relationships sometimes break, or sometimes the model says they work but the results are incorrect, plus what do the checkboxes mean for "assume referential integrity" & "apply filter security in both directions"

[@CurbalEN]

+David Benaim Great idea David! I will make more videos about relationships! /Ruth

[@learnspreadsheets]

Yay thanks! Cant wait :)

[@powershah]

Thanks Ruth! we are waiting for the videos on crossfilter "none" and "one" use cases Thanks.

[@mnowako]

Excellent and useful. PLEASE keep going Ruth!!! You are the Master of Masters in PBI :)

[@CurbalEN]

+Mariusz Nowakowski Thanks! I will keep going until you tell me to stop ;) Have a great Sunday! /Ruth

¡Excelente Ruth! como siempre un placer aprender de ti, gracias.

[@CurbalEN]

+José Rafael Escalante Hola José! El placer es mío sabiendo que mis vídeos son útiles!! :) /Ruth

[@ahmedal-dossary4386]

Extremely useful function. Thanks Ruth!

[@CurbalEN]

Enjoy your weekend! /Ruth

[@ahmedal-dossary4386]

You too!

[@jereyeses]

Como siempre de mucha ayuda... Gracias Ruth!

[@CurbalEN]

+Jesús Reyes Hola Jesus, genial y muchas gracias ;) /Ruth

[@sgk1966]

Thank you for bringing so much clarity to this.

[@CurbalEN]

+Gopa Kumar Hi Gopa, You welcome! Have a nice Saturday? /Ruth

[@pietrodaidone4659]

Hi Ruth, I've tried to add more columns on the same table using for both an expression with calculate and crossfilter. The first column was ok but the second generates an error of circular reference because the calculate on the first column change the filter context (Marco Russo explained it on his blog). Wolud be great if you could dedicated a video on this case.

[@CurbalEN]

+Pietro Daidone Hi Pietro, Could you please send me your sample file? I will try to make a video as soon as I can! /Ruth

[@pietrodaidone4659]

Hi Ruth the scenario: Table A : a list of components, Table B : for each component the registration of the dates in which it answered the call of the Agent A (the agent periodically verify if the component is active) Table C : for each component the date in which it answered the call of the Agent B ( as above ) for all the tables the key is "component_id" the objective is to add to the table A two columns with the last date recorded by Agent A and Agent B the relationship is off course one to many between Table A and either Table B or Table C. The expression used for each new column is =calculate(max(Table_X(date), crossfilter(TableA(component_id), TableX(component_id);Both) X= B, C the first column is created correctly, when you try to add the second one you take and error of circular dependancy

[@CurbalEN]

+Pietro Daidone Hi Pietro, Please post your question in the power bi community with some sample data. Easier to understand what you are trying to do :) /Ruth

[@SamirHabib]

Thank you Ruth, it was very useful for me!

[@shahadatshajjat8503]

You made those so easy to get, THANKS!!!

[@avalondsouza2002]

Great video Ruth. Just a tip. I noticed you were finding it a bit difficult to explain the relationships in the relationship view. One good tip I've learned is to put all the fact tables on the bottom and the dim tables on top. In that way, in a future video, a viewer can easily see and understand the relationships in your model from a first glance.

[@CurbalEN]

Thanks! :) /Ruth

[@RohitKumar-ds7dd]

Hi Ruth , Did u make the video for activating and deactivating relationships virtually , if yes can u please share the link. Thanks,

[@CurbalEN]

+Rohit Kumar Hi Rohit! I made a video about CROSSFILTER, have you seen it? Just google "crossfilter Curbal" and you will find it, /Ruth

[@RohitKumar-ds7dd]

Yes , i commented on the same video ( CROSSFILTER ) , but in this at end u said u will make new video about activating and deactivating relationships virtually . Not able to find that video.

[@CurbalEN]

+Rohit Kumar Hi Rohit, Ah! Userrelantionship! Check this one out: m.kzbin.info/www/bejne/qYCxp5aqhdGjors /Ruth

[@RohitKumar-ds7dd]

Thank you , this will help a lot.

[@CurbalEN]

+Rohit Kumar My pleasure! :) /Ruth

[@eladiobardelli3001]

fantastico Ruth , idola saludos desde CHILE

[@CurbalEN]

Gracias Eladio!! Saludos para ti también :) /Ruth

[@prachisingh5169]

All your videos are great but sorry, I didn't get one part, could you please explain? why do we need to create a measure of count of productid in product table? I got the same result by using a measure(distinctcount(orderdetails(productid)) in order details table and did not have to use cross filter/bi direction relationship in this case.

[@MarkCBB86]

Hello Ruth, What would one do in the case that there is no relationship between the 2 tables, could one create a relationship within the measure so that the tables do not need to have a relationship?

[@CurbalEN]

Hi Mark, Yes, it is possible, I need to create a video about it. Depending on what you are trying to do there are different options. Userrelationship is one way :) /Ruth

[@kolimirahamthulla4556]

Hello Ruth, Can you able to make a video on "Performance Tuning" for the PBI Reports in order to improve the performance. Thanks in advance !!

[@BroadtreeSolutions]

That was very useful Ruth, thanks

[@CurbalEN]

+Broadtree Solutions You welcome and Happy Friday! :)

[@xela010894]

great video . it is possible to combine two measures for example(year and month) and apply it as a filter? thanks.

[@CurbalEN]

+alex bravo yes, probably! Put your case in the Power BI Community to get more detailed help! /Ruth

[@krishnakishorepeddisetti4387]

hi Ruth, can you please create a video on how to do countif and countifs and also sumif and sumifs on dax...it will be of great help thanks Kishore

[@CurbalEN]

+krishnakishore peddisetti Absolutely, it is on my to-do-list now :) /Ruth

[@adamowiczjacek6543]

Hi Ruth Your movies are awsome!!. I learnd from you a lot:) I've got a question about this video. Could you explain diffrence beetween you measure: Crossfilter = CALCULATE([No Products];CROSSFILTER(Order_Details[ProductID];Products[ProductID];both)) and No Prods = DISTINCTCOUNT(Order_Details[ProductID]) In my opinion work in the same way. maybe we dont need use crossfilter? Please explain it to me thank you very much

[@prachisingh5169]

I have the same doubt worked fine for me even without using crossfilter.

[@xinzhuang838]

Thanks Ruth for this course ! The example is really claire and that's very helpful for me as a beginner user of power bi.Have a nice day ! :)

[@CurbalEN]

Wonderful , glad to hear! /Ruth

[@kennymcmillan1116]

THANKS RUTH !!!! EXCELLENT

[@CurbalEN]

+Kenny McMillan You welcome! :) /Ruth

[@davidwang8270]

a great video. thanks.

[@CurbalEN]

Excellent! :) /Ruth

[@mattmatt245]

When you're getting data from SQL Server, will they be imported along with their pre-existing relationships or you have to manually recreate them in the data model ?

[@CurbalEN]

+Matt under Hi Matt, Under the options tab, you need to enable: import relationships from the data source. When you do, power bi will try to understand the existing relationships in the data source and import them into the model if he finds any. Hope this helps, /Ruth

[@mattmatt245]

I'm actually trying to import to excel 2013 data model through a wizard however option "add to data model" is greyed out and relationships are not present. Will it work only in power pivot ?

[@rrrprogram8667]

Hi ... long time no cccc..... what is the issue in making all the filters as bidirectional ??? can you make a video on this with example ...

[@CurbalEN]

+RRR program Absolutely, the problem is performance. I will make a video about it soon! /Ruth

[@allmazd]

Does it work in Power Pivot?

[@TheDistractionStudio]

Thank You Very Much! Very useful video.

[@CurbalEN]

+rajan77 You welcome rajan! A real pleasure :) /Ruth

[@bradj229]

Great video! Thank you!

[@CurbalEN]

My pleasure!

[@venkatarathnamvemula1986]

Can someone explain cross table in Power Bi that is similar in Qlik View ?

[@kumarashwarya2185]

I have three tables, one with group id and their usage. The next table with groups and all its details. The third with village and who are handling these villages. I basically want to calculate the names of the people who haven't pushed the data in the 1st table with group id and their usage. How is this possible ??

[@MCloudNet]

Csn you please do the same thimg in excel power pivot

[@titoguerrero24]

Excelente explicación Ruth

[@CurbalEN]

Todo un placer Eduardo! /Ruth

[@rgbbv1]

hi can you help how do running week and mouth as a slicer (in time table) thanks in advance

[@YteFromLA]

Thanks Ruth!

[@CurbalEN]

+yetagesu nahto My pleasure !!! :) /Ruth

[@YteFromLA]

Ruth do you have Video on how to convert column to rows in power BI..I have three columns for my report column1 = departments , column2= countofEmployees and column3=monthnames i would like to display coulmn3(monthnames)values as individual columns (Jan,Feb,March..) with department and countofemployee values as separate couln values...

[@CurbalEN]

+yet nat You should be able to unpivot the column using power query, have you tried? /Ruth

[@BigDataSport]

thanks in advance, could you upload pbix file, thanks ruth.

[@CurbalEN]

+David Fombella Pombal As my server is down, I can't load the file. I will do it when I am back from vacation. /Ruth

[@BigDataSport]

please upload file , dear ruth, thanks in advance

[@zrinkomr]

Hello, Wouldn't you achieve the same if you've created No Products measure on Order_Details table? No Products from Order Details = DISTINCTCOUNT(Order_Details[ProductID]) Then you wouldn't need crossfilter function nor relationship in both directions, right? I know you did it this way for the sake of explaining the function, just asking if it is the same in this particular case. :) Cheers!

[@CurbalEN]

+Zrinko Mršo Hi Zrinko, I did it this particular way to explain the function, nice catch! :) /Ruth

[@TrekTales7595]

How to crossfilter in the same table?

[@faizanalimohammed9692]

Extremly Helpfull. Thanks!!

[@aldoespinoza1347]

tHANKS YOU. FROM LIMA PERU.

[@SmartBimson]

Very interesting function Ruth, but still to advanced for me 🤔 but I'll remember when I need it... so thanks again very much for your interesting Video Ruth 👍🏻🙋🏼‍♂️🌞 btw how was the night from last Friday to Monday ? ;-) I guess midsummer party last weekend was great 😉👍🏻🎉

[@CurbalEN]

+SmartBimson It was a "dry" evening as we were biking the day after. It is a perfect bike day as everybody is ... you know ... a bit wet...😂 Next stop: holidays! I have to start scheduling videos so I don't leave you guys without them in the summer ... /Ruth

[@yaakovweldler9346]

Couldn't you just create a one to many relationship between the products table and the Date table? Would performance suffer in any way

[@CurbalEN]

Check this video before you activate many to many: m.kzbin.info/www/bejne/hJXVo52hq61lsNE /Ruth

[@yaakovweldler9346]

Curbal thanks for the quick response. Isn't the date table to products take a one to many not a many to many? Is the date field you're using but coming from the date table?

[@M4000-i9u]

Thank you

[@CurbalEN]

:) /Ruth

[@connectwithme3567]

Thank you:)

[@way2honey]

Ruth Alternative caring