bridge table

[powerbiexpert22]

i am trying to include columns from table t1 however getting below error, sample pbix file is mentioned along with sample data

pbix: https://drive.google.com/file/d/1fHk0ePJuYsYcGXVogyk063pbc0acUu4p/view?usp=drive_link

sample data: https://docs.google.com/spreadsheets/d/1JY5J813U4I0awzecHFoj5YRQLmZamSLz/edit?usp=drive_link&ouid=10...

[Ritaf1983 · Accepted solution]

Hi @powerbiexpert22 

I'm not entirely clear about the goal of what you're trying to achieve, but there's a logical granularity problem in your model.

The growth in table T1 is at the year level.

Table T2 is at the week level (a lower level).

Therefore, you're unable to establish the connections properly.

There's simply no data at the region and week level... the region data is at the year level.
So you can create visuals with region and year granularity only (if you want to get correct results)

Or something like that to get "something" :

This result is not correct, because the granularity problem

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

[Ashish_Mathur · Accepted solution]

Hi,

Effectively, T2 is your Calendar table.  In T1, create a Date column via this calculated column formula

Date = 1*("1/1/"&T1[year])

Create T2 with a calculated Table formula

Calendar = calendar(min(T1[Date]),max(T1[Date]))

Create other columns - week and weekid in T2.

Create a relationship (Many to One and Single) from the Date column in T1 to the Date column in T2.

Hope this helps.

[Ritaf1983 · Accepted solution]

Hi @powerbiexpert22 

I'm not entirely clear about the goal of what you're trying to achieve, but there's a logical granularity problem in your model.

The growth in table T1 is at the year level.

Table T2 is at the week level (a lower level).

Therefore, you're unable to establish the connections properly.

There's simply no data at the region and week level... the region data is at the year level.
So you can create visuals with region and year granularity only (if you want to get correct results)

Or something like that to get "something" :

This result is not correct, because the granularity problem

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