Create and Expand the column with all records.

[bipinbhaskarwar]

Hi,

As a newbie to Power BI, requesting help.

I am having a dataset like below,

ID	LOCATION	PACKAGE
1234	ABC	BASE
4567	DEF	GOLD
7900	GHI	SILVER
11233	JKL	PLATINUM
14566	MNO	PLATINUM
17899	PQR	GOLD
21232	STU	SILVER

The list goes up to a million rows, but every package has different numbers of products in them. Like below.

PKG

PRODS

MANUFACTURER

PKG

PRODS

MANUFACTURER

PKG

PRODS

MANUFACTURER

PKG

PRODS

MANUFACTURER

BASE

11

XYZ

SILVER

11

XYZ

GOLD

11

XYZ

PLATINUM

11

XYZ

BASE

12

XYZ

SILVER

12

XYZ

GOLD

12

XYZ

PLATINUM

12

XYZ

BASE

13

XYZ

SILVER

13

XYZ

GOLD

13

XYZ

PLATINUM

13

XYZ

BASE

14

XYZ

SILVER

14

XYZ

GOLD

14

XYZ

PLATINUM

14

XYZ

BASE

15

GHY

SILVER

15

GHY

GOLD

15

GHY

PLATINUM

15

GHY

BASE

16

GHY

SILVER

16

GHY

GOLD

16

GHY

PLATINUM

16

GHY

BASE

17

GHY

SILVER

17

GHY

GOLD

17

GHY

PLATINUM

17

GHY

BASE

18

QWE

SILVER

18

QWE

GOLD

18

QWE

PLATINUM

18

QWE

BASE

19

QWE

SILVER

19

QWE

GOLD

19

QWE

PLATINUM

19

QWE

SILVER

29

XYZ

GOLD

29

XYZ

PLATINUM

29

XYZ

SILVER

30

XYZ

GOLD

30

XYZ

PLATINUM

30

XYZ

SILVER

31

XYZ

GOLD

31

XYZ

PLATINUM

31

XYZ

SILVER

32

QWE

GOLD

32

QWE

PLATINUM

32

QWE

SILVER

33

GHY

GOLD

33

GHY

PLATINUM

33

GHY

GOLD

64

KLJ

PLATINUM

64

KLJ

GOLD

65

XYZ

PLATINUM

65

XYZ

GOLD

66

XYZ

PLATINUM

66

XYZ

GOLD

67

QWE

PLATINUM

67

QWE

GOLD

68

KLJ

PLATINUM

68

KLJ

GOLD

69

KLJ

PLATINUM

69

KLJ

PLATINUM

100

XYZ

PLATINUM

101

QWE

PLATINUM

102

GHY

PLATINUM

103

KLJ

PLATINUM

104

XYZ

I am unable to create the relationship between two tables because PBI says it must have some unique values.

I need the count of every product and count of every package through which this product is being delivered. 

How to do it?

I was thinking about generating a custom column where all the products shall be 'tabled' along with other details and then expanding it but don't know how to do it.

Will it do the trick? 

Please help me with it.

[v-caliao-msft]

@bipinbhaskarwar,

I am unable to create the relationship between two tables because PBI says it must have some unique values.

Currently Power BI not support many to many relationship directly. So when creating relationships, make sure that at least in one of the tables the value in your table are unique. 

Besides, you need to re-shape you table so that you can count the measure you want.

Sample Measure:

Measure = CALCULATE(COUNTA(Table1[PKG]),FILTER(Table1,Table1[PKG]="BASE"))

Regards,

Chalrie Liao

[bipinbhaskarwar]

Thanks for the suggestion.

Actually, i get the data of package-product table exactly as you have reshaped. 

The problem is getting the counts of products and counts of product through the certain package.

I am not sure how to get it.

Thanks though.