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

[@saradachoudhury8732]

Thank you !!! very useful information.

[@eminempollo]

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

[@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

[@smanzoli]

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

[@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

[@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

[@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

[@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

[@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

[@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?

[@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

[@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

[@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 😉👍🏻🎉

[@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

[@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.

[@giri41]

dynamically

[@NareshKumar-hw4nl]

Very helpful in understanding the concept, thx Ruth

[@CurbalEN]

You welcome :) Glad it was useful! /Ruth

[@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 ?

[@melvin9993]

Cool

[@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.

[@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.

[@sid0000009]

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

[@gioratamir9578]

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

[@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.

[@ahmedal-dossary4386]

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

[@giri41]

Could you please do a video change of dimensions by dax

Excelente como siempre! :D

[@CurbalEN]

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

[@henrytran4782]

Thank you

[@CurbalEN]

My pleasure :)

[@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

[@heraldfinch7010]

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

[@robinherrick2177]

No. Try lookupvalue.

[@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])

[@giri41]

Genius.. bravo

[@stevennye5075]

excellent!

[@CurbalEN]

Yey!! :) /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

[@varing.4098]

Thank you!

[@CurbalEN]

You welcome! /Ruth

[@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