Forum Discussion

Heinrich's avatar
Heinrich
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Connecting to csv and filtering

Hello

I have 2 csv's with following data

Name: Communication

  • Column: Date
  • Column: Time
  • Column: Outgoing number
  • Column: Incoming number
  • Column: Email
  • Column: SIP-Code

 

Name: List of numbers

Column: PhoneNumbers

 

I would like to create a visual that shows me only the data of csv "Communication", where the phone numbers of the csv "List of numbers" in "Outgoing" or "Incoming" appear.

 

Regards

 

JFM_12

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi all, thanks for the quick reply, I'll add more.

    Hi Heinrich ,

    Here are the data of my two CSV files

    You don't need to establish a relationship between tables. You can achieve your goal by creating a measure using the following expression:

    Incoming_number = MAXX(FILTER('1','1'[Incoming number] IN VALUES('2'[PhoneNumbers])),[Incoming number])
    Outgoing_number = MAXX(FILTER('1','1'[Outgoing number] IN VALUES('2'[PhoneNumbers])),[Outgoing number])

    Final output

     

    Best Regards,
    Wenbin Zhou

7 Replies

  • suparnababu8's avatar
    suparnababu8
    Icon for Super User rankSuper User
    1 year ago

    Hi Heinrich 

    try this 

       Communication with Listed Numbers = 
   CALCULATE (COUNTROWS ( Communication ),
       FILTER (Communication,
           Communication[Outgoing number] IN VALUES ( 'List of numbers'[PhoneNumbers] ) ||
           Communication[Incoming number] IN VALUES ( 'List of numbers'[PhoneNumbers] )
       ))
    • Heinrich's avatar
      Heinrich
      Icon for Post Partisan rankPost Partisan
      1 year ago

      Hi suparnababu8

      Thank you but then how would it look like

      1. Create the measure

      2. Create a relationship between "Communication" and "List of numbers"

      -> Which columns should be related

      3. Create a visual with

      -> How do I show this data containing all below and showing only data that has the outgoing or incoming of the list and the rest of the csv "Communication"

      • Column: Date "Communication"
      • Column: Time "Communication"
      • Column: Outgoing number "Communication"
      • Column: Incoming number "Communication"
      • Column: Email "Communication"
      • Column: SIP-Code "Communication"

      Regards
      JFM_12

      • Heinrich's avatar
        Heinrich
        Icon for Post Partisan rankPost Partisan
        1 year ago

        Hello

        I have done it.

         

        1. Create the measure within the table "Communication"

        -> this filters out all none phone-numbers which are not within the list of "List of numbers"

        2. Create a visual of the type "table" with following columns

        • Column: Date "Communication"
        • Column: Time "Communication"
        • Column: Email "Communication"
        • Measure: "Communication with Listed Numbers"
          -> Shall I filter "Show Items with value" "is not blank"
        • Column: Outgoing number "Communication"
        • Column: Incoming number "Communication"
        • Column: SIP-Code "Communication"

        Is that it? There is no need of a relationship between both csv's?

        Am I missing something?
        Regards
        JFM_12

  • dharmendars007's avatar
    dharmendars007
    Icon for Memorable Member rankMemorable Member
    1 year ago

    Hello Heinrich , 

     

    You need to create a relationship between the two datasets like the below.

     

    The phone numbers in the "List of numbers" table should match the phone numbers in either the Outgoing number or Incoming number columns of the "Communication" table.

     

    Once you create the relationship please create a measure using Contains Measure to find the list of numbers appearing in Outgoing or Incoming numbers.

     

    If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

     

    Thank You

    Dharmendar S

    LinkedIN 

     

     

  • Heinrich's avatar
    Heinrich
    Icon for Post Partisan rankPost Partisan
    1 year ago

    Hello Dharmendars007

    Thank you but could you specify how to do that?
    Regards
    JFM_12