You are genius!! THX from Russia! I spent two days to solve this problem
@MichaelRygaard3 жыл бұрын
To summerize : no you cannot define a relationship with more than one key - so if they are split into multiple columns - make a unique key
@RADACAD3 жыл бұрын
Yes, or alternatively if you have a proper data warehouse structure, then your tables have Key columns which can be used to connect them together.
@luisedugarridosoares4 жыл бұрын
Hi Radcad! I had used the merge column for a different use case and it had worked AMAZINGLY! Very well done the video!
@Cr0w143 жыл бұрын
This is it. I was looking all over the place for a solution. This video is a godsend. THANK YOU!
@jeffkrimmel2 жыл бұрын
Thanks for the tip. And like you mentioned in one of the comments, my tables are Excel dumps from other data sources to which I don't have direct access. So I don't have unique lookup values and have to form my own here. I'm only dealing with about 50,000 rows of data in each of two different tables, so using a concatenated column for relationships isn't a problem. This solution works perfectly. Thanks a bunch.
@curacaodatadriven63994 жыл бұрын
Hi Reza, what a coincidence, I encountered exact the same issue as in the video. I implemented the solution you demonstrated and it worked like a charm. Thanks
@nizlopist4 жыл бұрын
The merge function is useful. It will save me the pain of concatenating and typing a “formula” to add 2 columns
@oppopina8433 жыл бұрын
God bless you many times, many years over. A week long ordeal brought to a happy end. Thanks so much.
@nhtrungdaniel Жыл бұрын
very useful, I just learned something new and applied it immediately in my work. Thank you!
@marioaguilera8896Ай бұрын
Thanks a lot, the information here is very clear, your video made my day.
@RADACADАй бұрын
You are welcome!
@Leclipdujour Жыл бұрын
Hello, thanks a lot for the tip. I struggled a lot on this, and this works perfectly.
@kevinlabore172611 ай бұрын
Thank you -- I wish PBI allowed to connect multi-columns like Excel does-- and still do NOT understand since virtually everything else is nearly identical -- this is one of the only major differences
@AlbrechtJ2 жыл бұрын
Thanks for this helpful video.
@RADACAD2 жыл бұрын
You're welcome!
@reg-net89564 жыл бұрын
Screenshots are unreadable unless you have a magnifying glass; please get better capture software.
@micgiggs3 жыл бұрын
Hi Radcad, I am new to Power BI and I hope to do a support dashboard on whether a robot job ended according to the scheduled time slot. A job might be scheduled to run more than once (such as job ABC scheduled to run in 2 timeslot) Scheduled Job Timings: Job|start_time|end_time ABC|12:00 AM|1:00 AM EDF|1:00 AM|2:00 AM GHI|2:00 AM|3:00 AM JKL|3:00 AM|4:00 AM MNO|4:00 AM|5:00 AM PQR|5:00 AM|6:00 AM STU|6:00 AM|7:00 AM VW|7:00 AM|8:00 AM XYZ|8:00 AM|9:00 AM ABC|9:00 AM|10:00 AM JKL|10:00 AM|11:00 AM PQR|11:00 AM|12:00 PM compliance table: Job|end_time|Is_Compliance ABC|20-Feb-17 07:02 pm|false EDF|5/10/2016 1:30:00 AM|true GHI|12-Dec-16 03:59 am| JKL|10-Feb-17 04:38 am| MNO|06-Feb-17 10:00 am| PQR|07-Nov-16 03:25 am| STU|20/1/2017 6:45| VW|08-Dec-16 02:52 pm| XYZ|16-Dec-16 04:19 pm| how do I populate the Is_Compliance value (true or false)? Would really appreciate some guidance. tx.
@bmdahl13 жыл бұрын
Thanks, you're a life saver
@panagiotisgiannopoulos2999 Жыл бұрын
That did solve my problem, thank you!
@databases22884 жыл бұрын
Thank you Reza for highlighting this workaround. But what if one of the fields is NULL? Will the concatenated field be NULL or NULL is converted to an empty value?
@RADACAD4 жыл бұрын
For handling nulls in this case, better to replace nulls with another value, like NA. And then do concatenation.
@anarakic28503 жыл бұрын
WOW! Amazing. You helped a lot ! :) If you want to have ONE SLICER for multiple tables then in edit query go to Add column, Merge column :)
@joshuagubler22592 жыл бұрын
Great solution!
@joaquinguzmanl2 жыл бұрын
This video was very helpful, thank you!
@RADACAD2 жыл бұрын
You're so welcome!
@pankulbaijal75423 жыл бұрын
Very nice trick and represents it very well. Thanks
@Alireza-ih5vu2 жыл бұрын
Perfect, Thanks Reza
@orkhannazarov27812 жыл бұрын
Thank you!
@alexrosen87622 жыл бұрын
Extremely helpful! Thanks :-)
@seshmishra83464 жыл бұрын
I am using ssas tabular model in excel, there is a heirarchy table connected to fact table and there is another table called list which is filtering heirarchy table, but issue is list table can only filter heirarchy if measure from fact table is used else it will not filter heirarchy table
@baffoesylvester87883 жыл бұрын
Very useful. Thanks.
@swapnilvmahajan3 жыл бұрын
Thanks. Solved my problem.
@Laxmanmane0073 жыл бұрын
Thanks for valuable video
@ryanmichaelhaley4 жыл бұрын
Thank you soooooo much!
@72132613 жыл бұрын
Thanks!
@yasarkhan47354 жыл бұрын
Thank you so much Sir
@TheTeguhdinatazt3 жыл бұрын
thankkyyou its really help me
@bitips2 жыл бұрын
Could you explain other methods ? Because I merge 3 columns to made a relationship , and my reports got to slow !!!
@RADACAD2 жыл бұрын
another method is concatenate the three fields
@rickystudds4 жыл бұрын
Reza thank you for the video but you are teaching potential new analysts to use concatenations to create relationships, I wish you understood that those habits are hard to break and fix!!! Out of all your content this is an effective way to introduce this potential disaster into someone's real business data model. I would consider putting some warnings in this video or revising what it teaches.
@RADACAD4 жыл бұрын
There is of course no problem in having a concatenated field. Not everyone have the luxury of having primary key fields and for some people that might not be needed. Having unique key that you can use is always the best, but real world scenarios don't have that option all the time.
@rickystudds4 жыл бұрын
Of course there's no problem with a concatenated field??? Let's say you have 20 tables that all use this concatenation method and a whole bunch mismatched data, the dax functions will not function correctly and the results will be off because of the blanks. Please comment on why this is not an issue?
@RADACAD4 жыл бұрын
@@rickystudds Mismatched data is totally a different problem. it can even happen if you have a primary key, foreign key. having a concatenated field, won't cause that. DAX functions would have issues if the quality of data, matching it, dealing with blanks are all sorted. which can happen with or without concatenated field anyways. I am not saying that people should have concatenated fields everywhere. I am proposing a solution for people without PK/FK to build reports without waiting for a week for a proper database to be built, and then be loaded with data through ETL process etc. Having PK/FK is a good thing to have (as I mentioned in the earlier comment), but we should not be obsessed with it and say everything without it is wrong! That contacted field in this case, can be a replacement of FK/PK.
@arthurhenriquesaraivademel33453 жыл бұрын
i havent tried this yet but i hope it works. In this case, after doing that, all the other informations from each table will be listed? For example, can i use some colum from table A as my numerator and some colum from table B as my denominator in a division operation?
@tomlazer11244 жыл бұрын
thanks - really helped me
@ImIronTurtl4 жыл бұрын
This is what I had to do to replace a number of merged queries which were impacting the success of my data load. Unfortunately, I have 13 tables to join to which correlates to the need to create 13 differing foreign composite keys. The result bumped my model size from 700MB to 2.4GB. Granted this works, but there has to be a better way WITHOUT going back to my Enterprise ETL tool and doing all the integer key creation there THEN sourcing to my PBI solution.
@RADACAD4 жыл бұрын
one other way is to create concatenated fields instead of integer fields. that way, you won't even need to use merge, and your refresh process would be much faster.
@b4uarvind2 жыл бұрын
Hi Radcad, I am stuck on a problem. I have two tables and want to map tables based on some condition on multiple columns. Like if in a row there is 1 then column A of table1 will map with column A of table2, if there is 2 in row then column B of table1 with map with column B of table2 and so on.
@udedomenico3 жыл бұрын
Hello Reza Rad and RADACAD, many thanks for this video explanation. Broadly speaking your solution works OK, but sometimes this approach could prevent visibility of data from one table to another, since in one of the tables the original fields are to be removed. My question is, how do we deal with similar scenarios? Merging tables? But could that not affect memory usage, since the resulting table becomes huge, having many duplications? Merging tables? that can create huge tables.... if that is the only solution then is PowerBI good enough to handle such huge tables? Thanks!
@roopalityagi39583 жыл бұрын
can we create column analogy in powerbi. Eg:Digitalization and Automation, i want to refer it as DNA in my graphs but in the slicer i want it as Digitalization and Automation. When i select the full form in the slicer it will filter the graph showing DNA in graph. Is it possible?
@fethiachour7114 жыл бұрын
many thanks. How can i use "Solver" in Power BI like in Excel?
@Tocy777isback04144 жыл бұрын
Genius! :O
@mardithpascua81122 жыл бұрын
Hello, I am new to PowerBI: I have two tables - Project List: * OwnerID * SupervisorID * ManagerID - User_Info_List: * UserID * Firstname * Lastname I need to have multiple relationship from OwnerID, SupervisorID and ManagerID to UserID (from User_Info_List), however, PowerBI only allowing me to have 1 Active Relationship. Is there a way for this? Thank you.
@waddahabdulsalam53214 жыл бұрын
Hi Thank you for sharing greet videos. I need help in creating multi-column relationship. I have a survey of 20 questions. Each question is placed in a column and each question has score rages from 1-5. These questions are in table 1. I created table 2 to translate the score to 1- strongly agree; 2 agree; 3 neutral; 2 disagree; and 5 strongly disagree. In creating the relationship between two tables, power bi doesn’t allow me to link table two to more than one column. How can I please help.
@snackymcgoo15393 жыл бұрын
I came here trying to fine out how to join FactInternetSales with FactInternetSalesReason. It just so happened one of those tables was being used in this examlpe. Both have SalesOrderNumber and SalesOrderLineNumber, in my belief there should be a SIMPLE 2 join on these fields. But I had to concatenate them like SO43729 & 1 = SO437291 to make them unique. I don't get it, but w/e.
@sahiladya84733 жыл бұрын
In my data I have year, country, state, city columns in a table. I am using this table as an master filter table. I have to filter these fields in another table using relationship. On one page using this master filter table - Year slicer can filter relevant countries in another slicer and country slicer filter relevant states and so on. On page two - master filter table should filter same fields using sync slicers feature. TO achieve that have created concatenate column in desktop and created relationship between master and 2nd table. But this is leading to many-to-many relationship. I have tried role playing dimension method which broke my master table in multiple distinct columns and I connected them with table I want to filter which helped me in one-to-many relationship. But this method is not helping in maintaining hierarchy - year filter country - state - city. is there any way around. please help
@syifachannel45848 ай бұрын
the saint
@derekpatick19234 жыл бұрын
Thank you for the video! Very Helpful.
@allaboutdata55232 жыл бұрын
Anyone knows how to convert start schema to snowflake schema and vice versa
@RADACAD2 жыл бұрын
I have many videos about how to transform your tables to star schema. how to build fact and dimension tables. check out them in this channel.
@sarbasov3 жыл бұрын
2:57 never join tables on concatenated fields. The query executions will last forever.
@RADACAD3 жыл бұрын
These tables are not necessarily SQL Server tables. If they were from a SQL database, they would have mostly likely had unique FK and PKs to use to create relationship. These tables are likely to come from Excel and many other non-relational data sources, and their query execution will not last forever.