Filter with 2 columns

[stoss]

Hello,

I would like to make a filter in one "recipient" column and filter also the identical values present in "MessageTracId" column.

If I make a filter in "recipient" column with => Not Contains  "Test1"
All the rows with MessageTracId "12" should be filtered.

Source.Name	_time	sender	recipient	MessageTraceId
1119_o365.csv	30/11/2019	Bob	Test1	12
1119_o365.csv	30/11/2019	Bob	Test2	12
1119_o365.csv	30/11/2019	Bob	Test3	12
1119_o365.csv	30/11/2019	Bob	Test4	12
1119_o365.csv	30/11/2019	Bob	Test5	13
1119_o365.csv	30/11/2019	Bob	Test6	13
1119_o365.csv	30/11/2019	Bob	Test7	13

Thank you very much for your help.

[parry2k]

@stoss not sure if I understood your request, you can add two slicer and choose recipient and messagetraceid as slicer column and once you choose value from recipient, it will filter 2nd slicer and vice versa

[stoss]

Thank for you answer, I will explain you my need.

In the below example, i want to count how many messages Bob sent to internal recipient ONLY.

_time	sender	recipient	MessageTraceId
30/11/2019	Bob	internal	12
30/11/2019	Bob	external	12
30/11/2019	Bob	internal	13
30/11/2019	Bob	internal	13

So if I make a filter with "internal", I will see 2 messages ID (12 & 13), but I want to count just when there are only "internal" recipients.

The result should be :

_time	sender	recipient	MessageTraceId
30/11/2019	Bob	internal	13
30/11/2019	Bob	internal	13

[parry2k]

@stoss what it the logic to exclude 12 when you selected internal?

[stoss]

@parry2k  because the messageId "12" contains a row with "external" recipient.

I need to count  MessageID with only "internal" value in "recipient" column.

As MessageId "12" is also associated to a row containing "external" value, I have to exclude it even if we have rows with "internal".

[parry2k]

@stoss solution attached, look at message table and page 4, there is other stuff in attached file, ignore that.

[stoss]

@parry2k  wow thank's a lot for your help.

I'm still trying to reproduce it in my PBIX 😞 

[parry2k]

Good luck and don’t hesitate to reach out in case you run into an issue.

[stoss]

@parry2k  I put my private data into your pbix and it doesn't work like excpected 😞

[parry2k]

@stoss that's not good. share sample data in pbix file and I will take a look. I probably missed something and doesn't have full picture on how your data looks like.

Removing sensitive information before sharing.

[stoss]

@parry2k you will find the pbix in the below link.

https://drive.google.com/file/d/153NtCOuGPQLGiUmJY8XsfVLmkO88FnKi/view?usp=sharing

The goal is to count only messages with gmail.com and/or yahoo.com.

The result should be 3, with following IDs :
123-456
456-789
252-973

[stoss]

@parry2k you will find the pbix in the link below.

https://drive.google.com/file/d/153NtCOuGPQLGiUmJY8XsfVLmkO88FnKi/view?usp=sharing

The goal is to count only messages with gmail.com and/or yahoo.com.

The result should be 3, with following IDs :
123-456
456-789
252-973

[v-lili6-msft · Accepted solution]

hi @stoss 

Just adjust the formula as below:

New Measure = VAR __rec = SELECTEDVALUE ( message[recipient] )
VAR __id = SELECTEDVALUE(message[MessageTraceId]) 
VAR __cc = CALCULATE( COUNTROWS( message ), ALLSELECTED( message[recipient] ), message[MessageTraceId] = __id )
VAR __cx =  CALCULATE(COUNTROWS( message ),ALL(message),message[MessageTraceId]=__id)
RETURN IF ( __cc = __cx, __cx, 0 )

Then use it as visula level filter:

Regards,

Lin

[stoss]

@v-lili6-msft Thank you for you help. Looks better now

As there is "MessageTraceId" present in the table, I can't count per month on one line.

When I remove "MessageTraceId", the table becomes empty.
I would like a table like that :

2019  December    3

Many thanks again @v-lili6-msft  and @parry2k for you help.

[v-lili6-msft]

HI @stoss 

For the row context of the visual has changed, the [New Measure] has been changed too.

So for your case, just add a new measure

Count MessageTraceld 
= VAR _TABLE=ADDCOLUMNS(SUMMARIZE(message,message[_time],message[MessageTraceId]),"_A",[New Measure]) RETURN
COUNTAX(FILTER(_TABLE,[_A]>=1),[MessageTraceId])

and remove the visual level filter of this table visual.

Regards,

Lin

[stoss]

It works, thank you very much @v-lili6-msft  and @parry2k