cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chris64
Frequent Visitor

Matrix is showing unexpected result..

I had 3 tables. Each table contains one xxxx-ID column, which acts as key. Table 1 only contains unique values of xxxx-ID, while table 2 and 3 contain multiple occurences xxxx-IDs (duplicates). Table 2 for instance has a column called 'action' with about 20 different actions. An xxxx-ID can have more than one action, but an action always has one of the same xxxx-ID. 

 

As I need to do some data validation I wanted to check how many of the xxxx-IDs from table 1 are also in table 2, for each 'action'. So I created a matrix visual. However, since I the results I got didn't make much sense, I added a new table (table 4) which contains a single column that has a single instance of all xxxx-ID in all three tables (so only unique xxxx-IDs).

 

Then, I created a new matrix visual to count the number of xxxx-ID per 'action' as shown below:

 

Power BI - screen1.png

 

As you can see, the numbers still don't make sense. For each 'action' it shows the count of all the xxxx-ID's in table 4. While some actions occur with just a couple of dozen xxxx-ID's.

 

My relationships looks like this. Basically I have table 4 connected to the three other tables by key xxxx-ID (marked yellow):

 

Power BI - screen2.png

 

 

3 REPLIES 3
Super User IV
Super User IV

Re: Matrix is showing unexpected result..

Well, unfortnately, because you blurred everything out, it's impossible to figure out what is related to what and correlate that back to your results. Or, you have created a relationship that doesn't make any sense. You shouldn't need a fourth table. 

 

Tough to really say without sample data, etc. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Chris64
Frequent Visitor

Re: Matrix is showing unexpected result..

You've got a point there... With the screens I tried to give as much info, without exposing sensitive data. I'll see if I can add some sample data.

Microsoft v-jiascu-msft
Microsoft

Re: Matrix is showing unexpected result..

Hi @Chris64,

 

Since all the IDs in the table2 are in the table1, it seems the answer is the count of IDs in the table2. Right?

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors