Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Applicable88
Impactful Individual
Impactful Individual

Looking for not equal rows

Hello,

I have two tables from the same source. The tables consist of machine data, for example there are OrderID's and the Datetime when the machine finished packing it. Unfortunately the leading primary key is not distinct, because the machine is reusing the same ID's after every few month. So I combined it in PowerQuery Editor with the Datetime to make a kind of composite key column, so now every entry is distinct. Since the first table has more then one entry per orderID I duplicate that and create a second grouped table where the entry is just existing one time. For simplicity reason I just add a date column not a datetime column.

 

Table 1:

OrderIDActionDatetime CompositeKey
1Fetch2021-05-01 101.05.2021
1Pack 2021-05-02 102.05.2021
1Finish2021-05-03 103.05.2021
2Fetch2021-05-01 201.05.2021
2Pack 2021-05-02 202.05.2021
2Finish2021-05-03 203.05.2021

 

Table2:

OrderID Datetime CompositeKey
1 2021-05-01 101.05.2021
2 2021-05-01 201.05.2021

 

Seemingly I should have a n:1 one relationship. My purpose is to use table 2 for better filtering and selection options, since every entry is distinct. But in the datamodel it says its a m:n relationship and I'm surprised. As far I know already one wrong row can transform it into m:n cardinality. Is there a way to quickly find the regarding rows, where I have more then 1 row of that entry in the second table? I hope someone knows how to find them.

 

Thank you very much in advance.

Best. 

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

Are you using the Composite key to create the relationship? If so, try making it a whole number instead (101052021, 102052021...)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
PaulDBrown
Community Champion
Community Champion

Are you using the Composite key to create the relationship? If so, try making it a whole number instead (101052021, 102052021...)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Very good idea. Thanks @PaulDBrown . After replacing all punctuation in the date with space and then combined the column with the order id I got a distinct primary key as a whole number to work with. 

CNENFRNL
Community Champion
Community Champion

Little useful info can be obtained from gibberish. One thing is for sure, PBI isn't traditional RDBMS; concepts such as composite key, foreign key aren't mandatory in modeling; whereas relationships and filtering matter.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors