Power BI logic for row based filtering

[ParthaCAT]

I have around 500 unique part numbers which are bolts used in Automobiles . Each part number has 10 variables like nominal diameter(Number) , shank length(Number) , material(Text) etc . There would be a condition like few set of bolts will have all 9 variables same and would differ by one variable , say for example , it is differing by material type . Assuming that , I am going to use superior material for all bolts , how to check the number of bolts that I can standardize [Suggest me the logic that I should apply in Power BI ] The source data is in Excel file as table

[Anonymous · Accepted solution]

Hi @ParthaCAT ,

I'm not clear about your requirement. Could you please provide some source data in the excel files (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[ParthaCAT]

Refer to part ID A2 & A3 . Please ignore the "part name" and "use qty" .Other than that all other column fields will be matching except "Coating" . Now if I am trying to replace the coating type from A to G , dash board should recommend only one part number (ie. For A3 , dash board should recommend A2) . Out of 2 part numbers , there should be only one .

At the outset , out of 22 part numbers , I should be able to see how much I could bring down by changing all coating type to "G". I am sorry that , I could not be able to share the power BI file .

[Anonymous]

Hi @ParthaCAT ,

May I know whether the following thread have the same requirement with current one? Actually, I still not clear about your requirement. What's your expected result? Do you want to get the count of part number which fulfill the conditions or something else?

DAX measure for comparing matching records among m... - Microsoft Fabric Community

Best Regards

[ParthaCAT]

A3

Hex head bolt

G

28

20

2.5

60

7.5

Full

Hex

N/A

None

steel A

A4

Hex head bolt

G

8

20

2.5

90

44

Pitch

Hex

N/A

None

steel A

A5

Hex head bolt

G

8

20

2.5

100

54

Pitch

Hex

N/A

None

steel A

A6

Hex head bolt

G

2

20

2.5

110

64

Pitch

Hex

N/A

None

steel A

A7

Hex head bolt

G

2

20

2.5

180

128

Pitch

Hex

N/A

None

steel A

A8

Socket head bolt

NA

4

20

2.5

45

7.5

Full

N/A

Socket

None

steel C

A9

Hex head bolt

G

5

20

2.5

70

7.5

Full

Hex

N/A

None

steel A

A10

Hex head bolt

G

4

20

2.5

50

7.5

Full

Hex

N/A

None

steel A

A11

Hex head bolt

A

24

20

2.5

60

7.5

Full

Hex

N/A

Sticky

steel B

A12

BOLT

A

184

20

2.5

80

34

Partly

Hex

N/A

None

steel A

A13

Hex head bolt

G

8

20

2.5

130

78

Pitch

Hex

N/A

None

steel A

A14

Hex head bolt

G

10

20

2.5

200

148

Pitch

Hex

N/A

None

steel A

A15

Hex head bolt

G

7

20

2.5

220

155

Pitch

Hex

N/A

None

steel A

A16

BOLT

A

4

20

2.5

120

74

Partly

Hex

N/A

None

steel A

A17

Hex head bolt

A

38

20

2.5

50

7.5

Full

Hex

N/A

None

steel B

A18

Hex head bolt

G

10

20

2.5

160

108

Pitch

Hex

N/A

None

steel A

A19

Hex head bolt

G

11

20

2.5

140

88

Pitch

Hex

N/A

None

steel A

A20

BOLT

A

10

20

2.5

150

98

Partly

Hex

N/A

None

steel A

A21

Hex head bolt

G

66

20

2.5

80

34

Pitch

Hex

N/A

None

steel A

A22

Hex head bolt

G

7

20

2.5

120

74

Pitch

Hex

N/A

None

steel A

[ParthaCAT]

Hi Yiruan . Thanks for feedback . I am sharing you the sample excel table for reference . I am sorry that , I couldn't able to share the Power BI file . 

Refer to part ID A2 & A3 . Please ignore the "part name" and "use qty" .Other than that all other column fields will be matching except "Coating" . Now if I am trying to replace the coating type from A to G , dash board should recommend only one part number (ie. For A3 , dash board should recommend A2) . Out of 2 part numbers , there should be only one .

At the outset , out of 22 part numbers , I should be able to see how much I could bring down by changing all coating type to "G".

Part ID

Part Name

Coating

Use Qty

Dia

Pitch

Total Length

Non Thread Length

Shank Type

Head(Ext Drive)

Head(Int Drive)

Locking Method

Material

A1

Bihex bolt

G

72

20

2.5

70

18

Partly

Bihex

N/A

None

steel B

A2

Hex head bolt

A

32

20

2.5

60

7.5

Full

Hex

N/A

None

steel A

A3

Hex head bolt

G

28

20

2.5

60

7.5

Full

Hex

N/A

None

steel A

A4

Hex head bolt

G

8

20

2.5

90

44

Pitch

Hex

N/A

None

steel A

A5

Hex head bolt

G

8

20

2.5

100

54

Pitch

Hex

N/A

None

steel A

A6

Hex head bolt

G

2

20

2.5

110

64

Pitch

Hex

N/A

None

steel A

A7

Hex head bolt

G

2

20

2.5

180

128

Pitch

Hex

N/A

None

steel A

A8

Socket head bolt

NA

4

20

2.5

45

7.5

Full

N/A

Socket

None

steel C

A9

Hex head bolt

G

5

20

2.5

70

7.5

Full

Hex

N/A

None

steel A

A10

Hex head bolt

G

4

20

2.5

50

7.5

Full

Hex

N/A

None

steel A

A11

Hex head bolt

A

24

20

2.5

60

7.5

Full

Hex

N/A

Sticky

steel B

A12

BOLT

A

184

20

2.5

80

34

Partly

Hex

N/A

None

steel A

A13

Hex head bolt

G

8

20

2.5

130

78

Pitch

Hex

N/A

None

steel A

A14

Hex head bolt

G

10

20

2.5

200

148

Pitch

Hex

N/A

None

steel A

A15

Hex head bolt

G

7

20

2.5

220

155

Pitch

Hex

N/A

None

steel A

A16

BOLT

A

4

20

2.5

120

74

Partly

Hex

N/A

None

steel A

A17

Hex head bolt

A

38

20

2.5

50

7.5

Full

Hex

N/A

None

steel B

A18

Hex head bolt

G

10

20

2.5

160

108

Pitch

Hex

N/A

None

steel A

A19

Hex head bolt

G

11

20

2.5

140

88

Pitch

Hex

N/A

None

steel A

A20

BOLT

A

10

20

2.5

150

98

Partly

Hex

N/A

None

steel A

A21

Hex head bolt

G

66

20

2.5

80

34

Pitch

Hex

N/A

None

steel A

A22

Hex head bolt

G

7

20

2.5

120

74

Pitch

Hex

N/A

None

steel A