Probably the most frustrating application that Microsoft has created.

[akee]

When it works, I've been able to create useful documents using PowerBI, but I swear on my ancestors that sometimes I hit a wall that doesn't make any sense.  Something that should be as simple as entering data will take me hours to figure out, even after searching community forums.  I've used most Microsoft products over the years.  I'm sure years of blood, sweat, and tears went into designing PowerBI, but I'm sorry, this is the one product that actually makes me angry when I try to use it.

The most current oddity is not being able to make a simple relationship between a list of course Id numbers and a table that shows an aggregate list of completed courses containing those Id numbers - filter according to the table of 12 values I entered.

In MS Access, this would happen like a flash.  No issues. It just does it.  Why in the world why another Microsoft product like PowerBI, make that so difficult?

Then, if I want to upgrade PowerBI for a new version where that issue might be resolved, I can never follow a straight path to upgrade.  😡  To all of you who love powerbi, no offense, to all of you who built it, I respect your efforts.  but this just takes me out.

[akee]

It is the inner join that I neglected to select.  I probably should have known that.
But I am confessing that here.  For others, here's the youtube video that helped me.

https://www.youtube.com/watch?v=ycb4qzo0nhE

[akee]

I will review whether an inner join is being performed by looking at the advanced text review of the DAX/version of SQL that they use.  Thanks for your time attempting to help me solve this. 

[smpa01]

What is your question actually ? This is not twitter. Post the same thing in stack and see what happens.

Ask your question with sample data and expected out put else report the issue else suggest an idea.

Power BI did not become a leader in BI for nothing mate !!!

[akee]

Hello SMPA01--

I appreciate your perspective, and as I acknowledged at the beginning of my rant...."when it works"....meaning, I have had some success.  It's these occasional tasks which seem like they should be so simple.

Here's the issue.

I have two tables:
a) bigsouthcourses (12 records, 2 fields, courseid, and coursetitle)

b) coursecompletions (850k records, 25 fields including courseid)

What I want is all the records in coursecompletions where the course ids are found in bigsouthcourses.

In SQL, if I simply wrote:

select  courseid, title from coursecompletions a

join bigsouthcourses b

a.courseid = b.courseid 
that would filter course completions.

In PowerBI, joining those two tables on courseid, and ensuring that the filter is applied mutually, does not yield the desired result.  I also followed instructions from other posts.

example:

bigsouthcourses

courseid	title
0920023501	Avengers Travel Tips and Tricks
0920023604	Cooking with Thanos
0920023719	Making Problems Disappear with Doctor Strange
0920023797	Learn to Speak Wakandan

coursecompletions

courseid	studentid	datecompleted
0920023797	h202	8/1/2021
0920023604	f232	8/23/2020
0920023797	m092	7/25/2021
0920023999	i202	6/25/2021

If I created a summarized card in PowerBI to count the related table count of student ids, the answer should be 3.

I will try what watkinnc suggested below, to use the merge button in the Power Query GUI to join the tables.  Simuntaneously, I've already made it happen in Tableau.  Let's see what works out best.

[smpa01 · Accepted solution]

@akee  you did not mention the RDBMS for SQL. But I have a background on MS-SQL only and if it is other than MSSQL, someone else will help you.

But if it is TSQL and your DDL is this

declare @courseCompleteion as table (courseId varchar(10), courseCompletion date)
insert into @courseCompleteion
select * from
(values('id1','2021-01-01'),('id2','2021-01-02'),('id3','2021-01-03')) t(a,b)


declare @bigSouthCourses as table (courseId varchar(10), tite varchar(10))
insert into @bigSouthCourses
select * from
(values('id1','t1'),('id4','t2'),('id3','t3')) t(a,b)

and you perform this

select  a.courseid, a.courseCompletion from @courseCompleteion a
join @bigSouthCourses b
on a.courseid = b.courseid 

to come to this

| courseid 	| courseCompletion 	|
|----------	|------------------	|
| id1      	| 2021-01-01       	|
| id3      	| 2021-01-03       	|

the equivalent syntax for Merging in power query would be INNER JOIN.

If I am not correctly interpreting you problem, please let me know in comments and I will try to help you. If it does not solve your problem and it is still TSQL, give a DDL too.

[Anonymous]

Oh. If you load both tables, you can use the Relationships dialogue to create the relationship. You can also do so from the modeling view. Additionally, you can use the Merge button in the Power Query GUI to join the tables on ID.

There are many very easy ways to do this, and that led me to assume that you weren't really looking for an answer. Please forgive my attitude!

--Nate

[akee]

Ok, thanks for the follow up.  I will try the merge option you suggested and report back (for other users who are "trippin'").

[akee]

I've tried several ways to merge queries as new, and that did not work for me.  Thanks for the suggestion.

[akee]

Are you a robot?  Seems like if you wanted to answer my diatribe as a human, you would have used your computer eventually to post a solution.

[Anonymous]

Correct answer: you must be trippin'.