Count rows containing string from other table column

[FabvE]

Hi,

I already tried some solutions ( like CountRows where column A contains a string and col... - Microsoft Fabric Community or Solved: Count rows containing value - Microsoft Fabric Community ) but that didn't help me.

My report has (in short) two tables:

Table 1: OwnerUnique

Columns:

• owner_mail: contains a single mail address per row as string

Table2: final_output

Columns:

• owner_final: contains a string with multiple mail adresses separated by comma
• virtual_path: an url as string

What do I need/want:

In a table visual I want to

• list all OwnerUnique[owner_mail]
• count all rows in table final_output where column [owner_final] contains the value from OwnerUnique[owner_mail]

At the moment the visual only counts these rows where the mail address is the only value but rows with multiple addresses are aggregated into a blank value in the visual.

My measure looks like this:

Kennzahl = CALCULATE(
COUNT(
final_output[VirtualPath_final]), 
FILTER(final_output, CONTAINSSTRING(final_output[owner_final], SELECTEDVALUE(OwnerUnique[owner_mail]))
)
)

Do you have any hinbts or ideas how to solve it?

Best regards

[rajendraongole1 · Accepted solution]

Hi @FabvE -  we need to refine the DAX logic to correctly evaluate rows with multiple email addresses. 

Kennzahl =
CALCULATE(
COUNTROWS(final_output),
FILTER(
final_output,
CONTAINSSTRING(
SUBSTITUTE(final_output[owner_final], " ", ""),
SELECTEDVALUE(OwnerUnique[owner_mail])
)
)
)

Alternatively, use a calculated table in DAX to create a normalized version of final_output.

Hope this works.

[FabvE]

Thank you both of you. Now it's working fine!

[AnkitaaMishra · Accepted solution]

Hi @FabvE , Tried with sample data as below : 

you can modify your dax as below : 
Measure =
CALCULATE(
COUNTROWS(final_output),
FILTER(
final_output,
NOT(ISBLANK(SELECTEDVALUE(OwnerUnique[owner_mail]))) &&
CONTAINSSTRING(final_output[owner_final], SELECTEDVALUE(OwnerUnique[owner_mail]))
)
)


Thanks, 
Ankita

[rajendraongole1 · Accepted solution]

Hi @FabvE -  we need to refine the DAX logic to correctly evaluate rows with multiple email addresses. 

Kennzahl =
CALCULATE(
COUNTROWS(final_output),
FILTER(
final_output,
CONTAINSSTRING(
SUBSTITUTE(final_output[owner_final], " ", ""),
SELECTEDVALUE(OwnerUnique[owner_mail])
)
)
)

Alternatively, use a calculated table in DAX to create a normalized version of final_output.

Hope this works.