Help with one to many relationships

[TXNathan]

Hi there,

I have been struggling to figure out how to make this happen.  I have a table with column headers that are account numbers and the index column are the fiacal periods. 

Under each account number are the amounts for that period.  I have  second table that has the first column as the account numbers and the rows are the different individual transactions that occur in the period.  This table has multiple entries per account number.

I have a visualization based on the first table that is a pie chart.  It also carries a drill through from the previous dataset that filters it  by period.  

When I try to drill through the pie chart slice it carrys the period filer through but not the account number.  I need the drill through page to have a table wiht all the charges for the account related to the slice I drilled on and for the period.  

Ultiamtely I cannot make the relationship from the tabel with the account numbers as headers (one) to the table with the multiple transactions per account number (many).  I ahve researched bridges, merged column keys, everything I can think of.  This has to be easy but I'm just missing something.

[danextian]

Hi @TXNathan 

The first table is not in the ideal format for reporting. Each account must have its own rows with their corresponding values. You will need to unpivot that in the query editor. Right click the period column and select Unpivot Other Columns

Create separate dimension tables for the periods and accounts and use them to bridge the two tables.

Create measures and use the columns from these dimension tables.

Please see attached sample pbix.

[TXNathan]

I was hoping this would work but its the same issue I have had all along.  There is not usable link back to the ORIGINAL table data that I unpivoted.  this is the forth level drill through.  Initially I start with a P&L breakout where I have a bar chart with total direct costs.  I drill through that into the direct costs breakdown in a pie chart form.  Then I drill through each slice to see what accounts make up the direct costs.  Lastly I am trying to drill down and show the actual transactions for each account.  This is where its falling apart.  

I amy have to scrap the entire thing and try something else.  

I appreciate the attempt

[Ritaf1983]

Hi @TXNathan 

In order to establish a relationship between both tables at both the period and account level for drill-through functionality,
you need to perform an unpivot operation on the first table in the image and transform it from a wide format to a long format, similar to the second table.

Additionally, you need to create a dimension table for periods and a dimension table for accounts.
These two dimension tables will connect the fact tables and enable proper filtering and drill-through across both tables.

Guide for unpivot tables here:

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

Guide how to create relationships:
https://databear.com/adding-targets-in-power-bi-report/

(in your scenario the fields are account id and period)

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.