How to create the visual

[prashantg364]

Hi,

I have 2 tables in my data set.
Table 1 has 2 columns, Product ID & Category against each Product ID

Table 2 has multiple columns, showing service count of Category on a given date.

Ex 1: Product ID 123 has 3 Category - 1000,1001,1002 as shown in table 1

on 1 Jan, out of 3 only 2 category has service count i.e 1000 & 1001

Ex2: 

Product ID 126 has 5 Category - 2001, 2002, 2003, 2004, 2005 as shown in table 1

on 1 Jan, out of 5 only 3 category has service count i.e 2001, 2002, & 2005

Output i am looking for to check on a given date which category has how many service count and which category has 0 count -  as shown in Output table

Pls suggest



Table 1: Product ID-Category

Product ID	Category
123	1000
123	1001
123	1002
124	1551
125	1665
125	1666
126	2001
126	2002
126	2003
126	2004
126	2005
127	950
127	951

Table2: Product ID-Category-Service

Product ID	Category	Date	Service Count
123	1000	01-Jan	1
123	1001	01-Jan	1
124	1551	01-Jan	2
125	1665	01-Jan	3
125	1666	01-Jan	3
126	2001	01-Jan	1
126	2002	01-Jan	1
126	2005	01-Jan	1
127	950	01-Jan	4
127	951	01-Jan	2

Output Table

Product ID	Category	Date	1000	1001	1002	1551	1665	1666	2001	2002	2003	2004	2005	950	951
123	1000	01-Jan	1
123	1001	01-Jan		1
123	1002	01-Jan			0
124	1551	01-Jan				2
125	1665	01-Jan					3
125	1666	01-Jan						3
126	2001	01-Jan							1
126	2002	01-Jan								1
126	2003	01-Jan									0
126	2004	01-Jan										0
126	2005	01-Jan											1
127	950	01-Jan												4
127	951	01-Jan													2

[xifeng_L · Accepted solution]

Hi @prashantg364 ,

You can refer to below demo.

Demo - How to create the visual.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~