A visual that shows multiple sources used in more than one script

[Pavithra_Ramesh]

Hi,

I have Labs and then I have Sources from which these Labs get the data. 

I need a formula and the best suited visual to display all the sources and then highlight those sources that repeat across Labs. Could you please help?

Sample Data:

Lab	Source
A	S1
A	S2
A	S3
A	S4_CZ
A	S5_CZ
B	S6
B	S4_CZ
C	S5_CZ
C	S3

Expected Output:

Lab	Source
A	S1
A	S2
A, C	S3
A,B	S4_CZ
A,C	S5_CZ
B	S6

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = CONCATENATEX(VALUES(Data[Lab]),Data[Lab],", ")

Hope this helps.

[PavanLalwani]

To display sources that are used across multiple labs and highlight those that repeat, follow these steps:

### 1. **Formula for Repeated Sources**:
Create a **calculated column** or measure using DAX to concatenate lab names for each source:
```DAX
LabConcat =
CONCATENATEX(
VALUES('Table'[Lab]),
'Table'[Lab],
", "
)
```
This will list all labs using each source.

### 2. **Best Visual**:
- **Matrix Visual**: This is ideal to list **Sources** as rows and **Labs** as columns. Use conditional formatting to highlight repeated sources (those with concatenated lab names).
- **Table Visual**: You can also use a simple **Table** visual with conditional formatting.

Let me know if you need more guidance!

[Ashish_Mathur · Accepted solution]

Hi,

You may also use this Power Query code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQo2VIrVgTKNEExjBNMk3jkKwTOF8ZxAPDMEE67MGUWZM9S0WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Lab = _t, Source = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Source"}, {{"Count", each Text.Combine([Lab],", ")}})
in
    #"Grouped Rows"

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Measure = CONCATENATEX(VALUES(Data[Lab]),Data[Lab],", ")

Hope this helps.

[Kedar_Pande]

Create a Calculated Column:

Lab_Sources_Concat = 
CONCATENATEX(
FILTER(
'Table',
'Table'[Source] = EARLIER('Table'[Source])
),
'Table'[Lab],
", "
)

Table: Display the Lab and Source columns, along with the new concatenated column
Highlight rows where the concatenated labs include more than one lab. For example, you can apply a color rule where if the Lab_Sources_Concat column contains a comma (,), that row is highlighted.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Pavithra_Ramesh]

Thank you! This gave me direction. I got some errors (circular dependency, expression referes to multiple columns..) and hence I followed the below which worked:

1. Created a Calculated Table

DistinctSourceLab =

SUMMARIZE(

'Table',

'Table'[Source],

'Table'[Lab]

)

2. Created a Measure

Lab_Sources_Concat =

VAR CurrentSource = SELECTEDVALUE('Table'[Source])

RETURN

CONCATENATEX(

FILTER(

DistinctSourceLab,

DistinctSourceLab[Source] = CurrentSource

),

DistinctSourceLab[Lab],

", "

)