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.
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
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?
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
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.
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.
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
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?
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?
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!
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.
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
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.
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.
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?
@@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.
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.
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.
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.
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.
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.
