Forum Discussion

stoss's avatar
stoss
Frequent Visitor
6 years ago
Solved

Filter with 2 columns

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 Con...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

parry2k's avatar
parry2k
Super User
6 years ago

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's avatar
    stoss
    Frequent Visitor
    6 years ago

    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.

     

    _timesenderrecipientMessageTraceId
    30/11/2019Bobinternal12
    30/11/2019Bobexternal12
    30/11/2019Bobinternal13
    30/11/2019Bobinternal13


    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 :

     

    _timesenderrecipientMessageTraceId
    30/11/2019Bobinternal13
    30/11/2019Bobinternal13
    • parry2k's avatar
      parry2k
      Super User
      6 years ago

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

      • stoss's avatar
        stoss
        Frequent Visitor
        6 years ago

        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".