Mapping Data Tables

[Anonymous]

Hi-

Table 1 :

I have a Data Table which gives me actuals by Posting Period (1,2,3....12), Product Category (A,B,C,D,E...), Reason Code (R1,R2..), Reason Category (C1, C2, C3), Date , company code . 

Fiscal Year	Posting Period	Date	Product Category	Reason Code	Reason Category	$ Values
2022	1	5/5/2021	A	R1	C1	$ 120,000.00
2022	2	5/15/2021	D	R1	C3	$   15,123.00
2022	3	6/14/2021	A	R2	C2	$   75,000.00
2022	4	7/14/2021	B	R4	C6	$     5,065.00
2022	5	8/13/2021	C	R5	C7	$   24,000.00
2022	6	9/12/2021	G	R6	C8	$   89,000.00
2022	7	10/12/2021	E	R7	C9	$ 500,000.00
2022	8	11/11/2021	F	R8	C5	$ 340,000.00
2022	9	12/11/2021	H	R9	C4	$   56,000.00

Table 2:

Product Category	Annual Target (FY22 )
A	$               100,000.00
B	$               300,000.00
C	$               150,000.00
D	$               240,000.00
E	$               158,140.00
F	$               360,000.00
G	$               780,000.00
H	$                  45,000.00
I	$                  56,000.00

I have created a Dashboard using the Table 1 where I have bars graphs showing the data by Product Category, Reason Code, Reason Category and Posting period. I have added the Date in the slicer so that people can filter for particular period/time range.

What I want to add is the 'TARGETS' in each of the graphs from Table 2 so that the TARGET shows as Line in the bar graphs.

1. Do I need to create the Table 2 in a different way?

2. How should I map Table 2 with Table 1 so that both the tables are talking correctly?

3. Do I need to have a separate TIME TABLE . Right now I don't need one as the data in Table 1 is by Date.

Thank you!

@amitchandak @RosieL @AlexisOlson 

[parry2k]

@Anonymous you don't need these columns in table2, when you visualize use it from table1.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k 

With what tables I should create active relationships with. Ta

Table 1 has : Posting Period (1,2,3....12), Product Category (A,B,C,D,E...), Reason Code (R1,R2..), Reason Category (C1, C2, C3), Date , company code.

Table 2 only has : Product Category.

I have mapped the Product Category frm Table 2 to Table 1 and it has 1:many relationship and the visualization is working fine.

For the visualizations for Posting period , Reason Code and Reason category I'm using the columns from Table 1 only because that is the ony Table I have and if I have to create an active realtionship of these columns  - With what column I should map these columns to?

[parry2k]

@Anonymous reason you will get this error is that the columns you are using in the crossfilter function are not used in the relationship for these tables. Make sure tables have an active relationship on the columns you are using in the crossfilter

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k 

Table 2 does not have the columns Posting Period (1,2,3....12), Reason Code (R1,R2..), Reason Category (C1, C2, C3), Date , company code ? So that is what I'm trying to figure out. How do I modify my Table 2 to have these columns included so that I can map it.

[Anonymous]

Hi @parry2k 

I'm getting the below error when I create the measure

"CROSSFILTER function can only use the two column references participating in a relationship"

[parry2k]

@Anonymous one way is to change the relationship cross filter to both which is not a recommended method, so better to create a measure that forces the cross filter to both

Sum Value = 
CALCULATE ( 
   SUM ( Table1[Value] ),
   CROSSFILTER ( Table1[Category], Table2[Category], BOTH )
)

 and use above measure in all the visuals.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Anonymous is the product category that contains a unique value in table 2? if yes, then you can set a relationship on category between table 1 and table 2, which will be one to many relationship, one on table 2 side.

In visual, use category and target from table 2 and the rest of the columns from table 1 and that will do it.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k ,

Yes the Table 2 has unique values of product Category.This is only working for the bar graph by product category. 

I also have graphs by Reason Code, Reason Category and Posting Period. How to make Table 2 to talk to the Reason Code, Reason Category and the Posting Period Columns so that the TARGET is reflected in the other graphs as well.