Duplicate rows when bringing data from multiple tables

[ImprintGroup]

Hello,

I am very new to Power BI and I am building my first report. I'm having an issue where I'm getting multiple rows of values that relate to a single record. It's hard to describe the problem so I'm going to show it as best I can. The data source is an SQL database hosted in Azure. 

Table 1 contains top level accounts. Table 2 contains client programs associated with the accounts. Table 3 contains individual events associated with each program from Table 2. Table 4 contains the financials for the program and are linked to Table 2 and not Table 3.

Looking at my screenshot you'll see the bottom 6 rows are for the same account and the same program. But there are 6 events associated with the program. 

1. how can I get the Account Name column and Program Name column to only show one instance of the account and program names and not show it in each row for each EventName? I'd rather those be blank.

1. Same question for the financials. You'll see $43,673 listed 6 times, one for each different EventName. But the financial number is attached to the ProgramName. So I'd like the financial to show just once for the ProgramName and not each time there is an EventName associated with the program.

I've been banging my head against my desk for the better part of 2 days trying different things I find via search engines. But they either don't work or I don't fully understand how to implement.

Thank you very much!

[v-luwang-msft · Accepted solution]

HI @ImprintGroup ,

Based on your current description, it is caused by the fact that you have a 1-to-many relationship between your table and the table before it. Just like in database, using left join to query two tables, if the fields in table 1 have more than one corresponding value in table 2, this will happen. Your current view is that there should be multiple correspondences between table 3 and other tables.
Also, according to your description, you only want to display one data in table 3, you can generate a new table based on table 3, and then create a relationship with the new table.

Sample:

tableA:

tableB:

relationship:

Output:

Step1,cancel relationship:

Step 2, add index on the tableB:

Step 3, base on table B ,create new tableC:

rank1 = RANKX(FILTER(TableB,TableB[index]=EARLIER(TableB[index])),TableB[Index.1],,ASC,Dense)

Step4 ,create relationship ,and new visual:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[AllisonKennedy]

@ImprintGroup  this is the difference between using the 'matrix' visual in Power BI and the 'table' visual. Use a 'matrix' visual and you will get blanks for repeated program.

[ImprintGroup]

@AllisonKennedy 

I'm trying to use the Matrix visual now. While I struggle with not being able to visually represent each data point across the row in columns, as I add the financials I still end up with repeating amounts for each line where there is an event.

[AllisonKennedy]

@ImprintGroup You can turn stepped layout off under options, then need to play with formatting: 

As for your repeated values, do you want it blank? 

Try using ISINSCOPE in a measure:

New Value = IF ( ISINSCOPE( table[event], BLANK(), SUM(table[value]) ) 

[ImprintGroup]

@AllisonKennedy This has me closer. But I can only see 8 out of about 16 "columns". I'm unable to scroll horizontally. 

I would prefer the repeated values be blank and I'll see what I can accomplish with your suggestion on the measure.

[ImprintGroup]

I tried Remove Duplicates in Power Query but it had no effect.