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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Michael-Lowden
Advocate I
Advocate I

How do I get PowerBI to respect my JOINS?

Seriously!? I join My Pre-Filtered Calendar to a wide-open Labor table in a 1:N relationship and it results in a FULL-OUTER and messes up all my KPIs ... everything.
I'm so utterly confused by PowerBI's engine. after 20 years in the BI space the engine repeatedly does the exact opposite of what I'd expect.

Any thoughts?

--thanks.zJXKsHt

hmiThjr

Is there a way to tell PowerBI to respect INNER-JOINS always? Even if I am not crossing queries? Take the middle table above. It's  a standalone table. Do I have to add another layer of filters on this to get it to only show me 3-weeks work of data? ... based on the table it's joined against? 

 

 

10 REPLIES 10
v-haibl-msft
Employee
Employee

@Michael-Lowden

 

You can try to use the visual level filter as following screenshot to exclude Blank value.

 

How do I get PowerBI to respect my JOINS_1.jpg

 

Best Regards,
Herbert

Ya. I've ended up applying this at the report level. Not the Viz leve. And what I've decided to use is the same field I join on at my central table. The "1" side of a 1:* I do an Advanced Filter on choosing "Is Not Blank". and visually it's purging my results. I just don't trust all my other results without redoing all the math myself against the raw data. Then figuring out how to convince PowerBI to give me the same number is always fun ;). This is 'part' of my solution. ... cheers.

Greg_Deckler
Super User
Super User

Looks like you have some unmatched rows between your two tables. Is your Week_day in your table on the N side really a weekday and not a date? In general, I think you might perhaps be working too hard for this. If your Week_day is really a date as your relationship with your Calendar table suggests, you could just throw it into a slicer and you can use the slicer to dial it back to just 3 weeks. No need for a second table.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for the quick reply. Don't get hung-up on the field being called "Week-Day" ... but yes, it's simply a Date. This is just the most basic example I could come up with to represent my frustration. My actual frustration is ~200x worse than this example.
PS -- I know how to hack my way around this when looking at just the "Labor" table. But in actuality I dealing a 'big-data' transactional system (full global SAP stack and orders with ~750,000 line items each) that loads 15 source tables daily. I've broken it down to a basic star-join. Then, 300+ KPIs on an Executive dashboard later. If I have to go to each of the visuals, and control everything by peripheral queries instead of the one central query ... which is it's job. I'm going to write my own BI tool, cause I think it'll take less time.

You might want to investigate NATURALINNERJOIN DAX function and related functions or the Merge capabilities in M code that allow you to control how the join is performed.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

ok. so I got that query working. in my test report.
so the process to get inner-join data is:

1. add table 1

2. add table 2

3. setup relationship between table1 and table2

4. create a new query to join these tables

5. use new query columns instead of source table columns

 

so. that said. ... does this mean that for my 10 tables (1 central, 9 peripheral) that I need to 9! new queries? that's 9-factorial, actually it's more like 9+8+7+6+5+4+3+2+1=45 queries. As to relate data across perihperals I'm going to then have to create relationships between the 9 new queries (that's 8), then 8 new NATURALINNERJOIN queries, and so on. Until I have 1 master query that is the final INNER-JOIN product of my 10 original tables?

--thanks again.48wGeYl

 

 

 

Without seeing an example of your data model and a firm understanding around what you are trying to accomplish, very hard to tell you anything specific.

 

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

It's simple. I have 10 tables. 1 of which is a critical central fact table that is filtered to data that's of interest for the report. Be it a calendar filter, or a regional filter, or a departmental filter, whatever. And 9 1:N dimension tables from there. And a need to give PowerBI to a business user to do data pivoting. I can setup the PBIT to have a foundation of formulae and table relationships. BUT. This is supposed to be an adhoc tool that so far only produces bad data. I chase the data only to discover that PowerBI is carteseaning my SUMs, de-scoping my AVGs, etc. Works beatifully until you go across queries. Which I accept is hard across child tables. But my above example is as simple as it gets and it still produces an "other" record that frankly shouldn't be there by default. I'm sorry I'm venting. I'm just frustrated that every "easy" question I have results in custom code to resolve. And people claimed Crystal Reports was too "programmy".

Hi @Michael-Lowden,

 

Have you tried changing the Cross filter direction from Both to Single?  (Double click on the join line in the relationship view to get to the edit relationship window.

 

HTH

 

The Single-vs-Both thing has constantly eluded me. The closest I've come to understand it, though you define it as part of the joining relationship, it doesn't do anything for the join itself, but does affect how Viz's cross affect eachother's data via interactions. But only once you interact. ... I think.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.