Issues in Matrix visual

[simrantuli]

Hi All,

I am facing an issue while trying to show some information in 'Matrix'. 

So, I have below two tables.

'Table' table:

'Mapping' table:

What I want to show in 'Matrix' visual is 'Site' as Row, 'Service' as Column and 'Supplier' as Value.

So, for Value, I have written the below DAX and used it in 'Value' in Matrix but it doesn't show me the correct information.

Supplier_Name = SELECTEDVALUE(Mapping[Supplier])

So basically, Matrix doesn't show values for 'B3' and 'B4'. And for others too, it doesn't show correct values.

Your help would be greatly appreciated 🙂

Best Regards

Simran Tuli

[simrantuli]

This is the output that I am getting. The values are not showing correct.

[Fowmy]

@simrantuli 

If you are expecting multiple supplier values to come in the value areas, try the following measure.

Share data in Excel format or just paste in the reply to box to check further.

Supplier_Name = 
CONCATENATEX(
    VALUES(Mapping[Supplier]),
    Mapping[Supplier],
    UNICHAR(10)
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[simrantuli]

Hi @Fowmy,

I am not expecting multiple values in Matrix as if you see the data also, there are no multiple suppliers for a single site and service combination.

Please find below the data.

'Table' table:

Service   #Key_Site_Supplier

Food	A1##A
AV	A2##B
Fitness	A3##C
Food	B1##D
Fitness	B2##A
Food	B3##A
Fitness	B3##A
AV	B3##B
AV	B4##A
Food	B4##B

'Mapping' table:

Site Supplier #Key_Site_Supplier

A1	A	A1##A
A2	B	A2##B
A3	C	A3##C
B1	D	B1##D
B2	A	B2##A
B3	A	B3##A
B3	B	B3##B
B4	A	B4##A
B4	B	B4##B

Best Regards

Simran Tuli

[Fowmy]

@simrantuli 

Do you have a single table or two tables with a relationship? 

You can share both if so

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[simrantuli]

Hi @Fowmy,

I have two tables with a relationship.

'Table' table:

Service   #Key_Site_Supplier

Food	A1##A
AV	A2##B
Fitness	A3##C
Food	B1##D
Fitness	B2##A
Food	B3##A
Fitness	B3##A
AV	B3##B
AV	B4##A
Food	B4##B

'Mapping' table:

Site Supplier #Key_Site_Supplier

A1	A	A1##A
A2	B	A2##B
A3	C	A3##C
B1	D	B1##D
B2	A	B2##A
B3	A	B3##A
B3	B	B3##B
B4	A	B4##A
B4	B	B4##B

I want to show 'Site' as Row, Service as 'Column' and 'Suppliers' as Values in Matrix.

Thanks!

[Greg_Deckler · Accepted solution]

@simrantuli - I did it like the following:

Measure = 
    VAR __Key = MAX('Table'[#Key_Site_Supplier])
    VAR __Site = MAX('Table (2)'[Site])
RETURN
    MAXX(FILTER('Table (2)',[#Key_Site_Supplier] = __Key && [Site] = __Site),[Supplier])

PBIX is attached below sig.

Also, you could achieve this just using "First Supplier" if you make your relationship filter direction "Both". Then you don't need the DAX. But, if for some reason you can't do that in your model, the DAX should work.

[simrantuli]

@Greg_Deckler You are AWESOME!

It worked. Thank you so much!

Just one question. Why doesn't the below measure work in this case? What's the issue? If you could explain. 

SELECTEDVALUE(Mapping[Supplier])

Thanks!

[Greg_Deckler]

@simrantuli - I will have to test but off the top of my head, I would say it is because your relationship between your tables is not set to a filter direction of Both. You want 2 arrows pointing in each direction on your relationship. If not that it could be that SELECTEDVALUE is finding multiple items but in that case it would return blank by default I believe. 

[simrantuli]

Thanks @Greg_Deckler