Match Multiple Values in a cell with another cell

[Anonymous]

Hi,

I have a dataset as below:

Name	Size	Type	Tier
A	100 200 300 400	Unmanaged Unmanaged Managed Managed	Standard Standard Standard Premium
B	100 200 300 400	Unmanaged Managed Unmanaged Unmanaged	Premium Premium Premium Premium

In Name A, I have four values on other cells. I want to match like below:

Name: A

Size: 300

Tier: Standard

Type: Unmanaged

Name: A

Size: 300

Tier: Standard

Type: Managed

Name: A

Size: 400

Tier: Premium

Type: Managed

Likewise:

Name: B

Size: 800

Tier: Premium

Type: Unmanaged

Name: B

Size: 200

Tier: Premium

Type: Managed

I tried the below method but failed:

Split the Columns by rows. When i did that the Size rows are also increasing, hence the count doesn't happen correctly.

Any other methods please??

[JosefPrakljacic]

Hello @Anonymous 

here you have my example file

First I filled in all your lines with the names (A,B) in the PowerQuery Editor with the fill down option.

And this table was loaded into PowerBI from then on it was easy to achieve your desired result.

If this post was helpful may I ask you to mark it as solution and give it a 'thumbs up'? This will also help others

Have a nice day!

BR,
Josef
Graz - Austria

[Anonymous]

Hi. Thanks for your response and sorry for the late response.

Data set is not like you mentioned. It is like below:

When I spli Size, Type and Tier by Rows, the data becomes as below:

And the list goes on...................... I am not able to get the correct sum of the Size according toType and Tier.

Please help me with this!! Thanks in advance.

@JosefPrakljacic 

[Ashish_Mathur]

Hi,

Drag Type and Tier to your Table visual and write this measure

=SUMX(VALUES(Data[Name]),MIN(Data[Size.]))

Hope this helps.

[Anonymous]

Hi,

I doesn't.

I split the Size column into rows and then calculated the sum of Size. It returns me the sum correctly. But still the Tier and Type I couldn't the achieve the expected result.

Any other headsup please!!!

Regards,

Sarath Kumar S

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

https://drive.google.com/open?id=1R4LISdRTpNS8yLUCswB4-VTLVE1qDMpo

Please find the link above to download PBIX.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks a lot @Ashish_Mathur . It really helps!!

[Ashish_Mathur]

You are welcome.

[Anonymous]

Hi, @v-lid-msft ,

Can you please help me with this??

Thanks in advance.

Regards,

Sarath