DAX Fridays! #27: RELATED vs RELATEDTABLE

In today's video, we will go through two functions in DAX, related and relatedtable.
We will explain how they both work and when to use one or the other.
Link to Power BI file used in the video: curbal.com/blo...
EXCEL SURVEY: 1drv.ms/xs/s!A...
PREVIOUS VIDEO: Break ties w rank: • DAX Fridays! #26: Brea...
NEXT VIDEO:
Looking for a download file? Go to our Download Center: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
Join our DAX Fridays! Series: goo.gl/FtUWUX
Power BI dashboards for beginners: goo.gl/9YzyDP
Power BI Tips & Tricks: goo.gl/H6kUbP
Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
#daxfridays #curbal #SUBSCRIBE
► Linkedin: goo.gl/3VW6Ky

[@anjankumar2517]

Just want to say Big thank you Ma'am! I really appreciate your effort. Kudos to you! my words are not enough ! Super Women!

[@CurbalEN]

Thanks!

[@diego2538]

Gracias por el video! Hasta ahora en los casos de 1 a * hacía merges en power query para poder hacer cálculos entre tablas. Esto es justo lo que necesitaba. Un saludo

[@Darknesslc3]

thank you so much. I was wondering why use related when we already define the relationship, turns out the filter cant directly refer to other table

[@sumitnegi9843]

Please repy can we use RELATED and RELATEDTABLE in both table by adding new measure or column if we apply both directional in relationships?

[@Ikramikram-vf6fb]

Hello Curbal I adore all your videos but I have a remark, for the related fucntion I use it now for the *:1 relationship not sure if it was not the case before but now if we want to calculate MstockValue = SUMX(Stock,Stock[Qty]*RELATED('Product1-1'[Cost Component])) and the * side is the Stock table for example it work fine

[@CurbalEN]

I have a part 2 where I correct my mistakes

[@NareshKumar-hw4nl]

Very helpful in understanding the concept, thx Ruth

[@CurbalEN]

You welcome :) Glad it was useful! /Ruth

[@rolandkc]

I did not know this function. Thank you, Ruth.

[@CurbalEN]

+Roland Kc You welcome Roland! /Ruth

[@graemegourlay2850]

Great tutorial. I used this method to create a sort order for value on an axis. Other methods I tried were giving circular reference errors.

[@SmartBimson]

Hey Ruth, interesting video again 👍🏻 I usually did it in vlookup in excel and then used it in power bi, but now I'll use Dax I guess ! Thanks a lot again , esp for the Dax Friday 👍🏻👍🏻👍🏻

[@CurbalEN]

+SmartBimson Great! Better to do it in Power BI! Happy Friday Bart Simpson :) /Ruth

[@SmartBimson]

Curbal 😉👍🏻🎉

[@skillguru286]

Thank you for your video- I have 5 tables and i want to have subset of the table (New) with limited number of columns. I have a column which is common in all 5 tables. How will i do this?

[@lakshmischannel1884]

you can write select columns and Union I think so.. please try once

[@ahmedal-dossary4386]

Amazing video, well-documented and summarized in 7:42.

[@heraldfinch7010]

Can you use these functions when two table don't have any relationship between them? Thanks

[@robinherrick2177]

No. Try lookupvalue.

[@eminempollo]

The difference it that related allows just for one to one relationships while related table allows for one to many...

[@giri41]

Could you please do a video change of dimensions by dax

[@sid0000009]

Can we have calculated tables inside RELATE function - Using that in Visual Studio for Data Modelling

[@smanzoli]

Related also allows many to one.... but yes, not one to many.

[@sgk1966]

Hi Ruth...First I need to appreciate you for the effort, understanding and time to put out these highly helpful videos. Thank you very much. I just have a doubt on this video...regarding the data you have. I know it is sample data to explain the concept. But just curious how it would have worked out, if the item with two costs had any stock balance. In this case, how would have power BI calculated the stock value?

[@CurbalEN]

Hi Gopa, thanks! Glad you like my videos and find them helpful! :) Could you be more specific on your scenario, not sure I understand it. /Ruth

[@sgk1966]

I have tweeted you with more details..Thank you..

[@CurbalEN]

+Gopa Kumar Hi, I have got any, which account did you use? /Ruth

[@sgk1966]

@ruthpozuelo

[@CurbalEN]

+Gopa Kumar I haven't received anything.. what is your twitter account? /Ruth

[@gioratamir9578]

Ruth, Why isn't it possible to author a measure like Mstockvalue with RELATEDTABLE?

[@SamuelRoshan]

How do I use Related function to get the other related table value? For example, in your video, you have onl two tables and related works well. But I have two tables that are linked via other tables inbetween. When I use Related, it only allows me to select the table that is adjacently connected, not the last table. Hope I was clear TAB1 TAB2

[@Hemantshyontry]

Samuel Roshan hi,did u get any answer to this?? I am also looking for an answer to this. Please let me know if u have got any explanation or way out to solve this.

[@CurbalEN]

Hi Hemant, The answer is:it depends. It depends on how the model looks like. To get an answer for your specific needs, post your question in the power bi community explaining how your model looks like and you will get help quickly. Have a nice evening :) /Ruth

[@texmexdragon2160]

In the download example, you have a 1:1 relationship between the tables, despite them having a different amount of Component records. I cannot get PowerBI to create a 1:1 relationship - Any ideas? I'm following the tutorial exactly but this issue does not allow me to continue. The other issue is that I see you get a sum on your Qty column (70), but in my version I do not get a total at the bottom for that column.

[@rafalg8575]

Hi, I have comment that „Product1-1[Cost Component]” column doesn't exist or doesn't have any relationship. It is not true because I can see relation between tables. What is the problem ?

[@jagadeeshpinninti]

Please can you help I have 2 table's Table 1 Column --> Region, opening_ARC Table 2 column --> Region, Pending_ARC I need to Show Region, Opening_ARC, Pending_ARC in table If i pick region from table 1 Pending_ARC is showing wrong values, If i pick region from table 2 Opening_ARC is showing wrong values

[@saradachoudhury8732]

Thank you !!! very useful information.

[@BernatAgulloRosello]

but if you are adding a calculated column in the "many" side, I think you can safely use RELATED, right? so you can't use RELATED to "jump" to the many side, so to say.

[@drcrod2000]

I reference this video a lot. I seem to not be able to remember this trick every time.

[@CurbalEN]

+Caroll R. I use my videos for my own reference too :) Have a nice weekend! /Ruth

[@jaishreeram4177]

Awesome explanation. Can you please make a tutorial video on how can we switch between N visuals using only a single button? We can do similar thing with chiclet slicer but it involves many button.

[@giri41]

Genius.. bravo

[@jeremiahlobo3727]

Correction needed here : RELATED Table can be used for 1-1 and 1-Many RELATED Table : Many :1 and Many : Many

[@CurbalEN]

I correct myself on part 2

[@anthonyscott4476]

70 * 225 = 15750 NOT 1810, but 460+430+920 is. The error is in the explanation at 4:10, as the totals at the foot are the summations of the columns, so for the last three components, although the each have costs, they have no Quantity, thus their MstockValue = 0 in each case, but a very informative and interesting video nontheless.

[@CurbalEN]

Thanks for pointing that out !! :) /Ruth

Excelente como siempre! :D

[@CurbalEN]

+José Rafael Escalante Muchas Gracias José, buen Domingo :) /Ruth

[@harishpaldhir5915]

Dear madam I have a problem can u help me I have a list which i use to make pivot for my report in which i have bill no, bill date, amount etc for payment collection from my customer but this table does not have when products r deliverd to my customer and by whom this information is in another table i want to combine these two tables using bill no but cannot combine these Can u help me on this

[@CurbalEN]

+HARISH DHIR Hi Harish, Could you please post the question in the Power Bi community site with some sample data? Thanks! Ruth

[@harishpaldhir5915]

Curbal Gd mng ruth I am very glad that u respond to my question i will post my question in power bi cummnity in 3 hours when i reach my office

[@harishdhir6048]

GD AFTERNOON DEAR RUTH IT IS DIFFICULT FOR ME TO PROVIDE U DETAILS OF MY PROBLEM BY TYPING HERE CAN U SEND ME YOUR EMAIL ID SO I CAN SEND SAMPLE DATABASE ALONG WITH MY PROBLEM TO U MY EMAIL ID IS [e-mail] i am new to youtube so difficult for me to explain my problem here it will be of great help if u give me your email id by sending me a mail on above email id

[@CurbalEN]

+Harish Dhir Hi Harish, please post your question and sample data in the power bi community. Thanks! /Ruth

[@harishpaldhir5915]

Curbal dear ruth I have posted my problem with sample database in power bi community did u get it

[@henrytran4782]

Thank you

[@CurbalEN]

My pleasure :)

[@stevennye5075]

excellent!

[@CurbalEN]

Yey!! :) /Ruth

[@giri41]

dynamically

[@varing.4098]

Thank you!

[@CurbalEN]

You welcome! /Ruth

[@priviyapaulraj2645]

in one table am having submitted, not submitted values another table am having submitted , not submitted values(i want to equal function with related) TABLE1 = TABLE2 EXPECTED SOLUTION submitted = submitted (submitted) notsubmitted= notsubmited (notsubmitted) submitted = notsubmitted (notsubmitted) notsubmited = submitted (notsubmited please kindly provide the solution shortly.

[@priviyapaulraj2645]

TABLE1(COLUMN1) = TABLE2(COLUMN1) EXPECTED SOLUTION(COLUMN1) submitted = submitted (submitted) notsubmitted = notsubmited (notsubmitted) submitted = notsubmitted (notsubmitted) notsubmited = submitted (notsubmited please kindly provide the solution shortly.

[@CurbalEN]

The power bi community is the perfect place for that, please check there! /Ruth

[@lakshmischannel1884]

@@priviyapaulraj2645 ExpectedColumn = IF(Compare_Columns[Column1]="not submitted","not submitted",Compare_Columns[Column2])

[@melvin9993]

Cool

[@scottmcgee935]

How frustrating. At 6:11 into the video, a flat out inaccurate, wrong, incorrect statement is made. "This is the difference between RELATED and RELATEDTABLE is that RELATED allows just for one-to-one relationship, while RELATEDTABLE allows for one-to-many". This statement is one-hundred percent wrong. I heard this, and assumed I had misunderstood, so I watched it again. And then again. And then I tested it. And then I tested it again. And it's just wrong. Absolutely and completely wrong.RELATED looks into a related table from the "many" side to the "one" side.RELATEDTABLE looks back the other direction, from the "one" side" to the "many" side, and returns a table.Horrible.

[@CurbalEN]

Watch part 2, it gets better there ;) /Ruth

[@JoshuaDHarvey]

Jeez dude... how do you really feel? lol

[@punit6490]

Unnecessarily long video without flow...

[@CurbalEN]

Happy New Year!! 🎈 /Ruth

[@JoshuaDHarvey]

Dude, it's not all about you, for some people it's helpful. Be polite!

[@EnneagramTarot-zu8kp]

5:50- RELATEDTABLE