Forum Discussion

Katek10's avatar
Katek10
Frequent Visitor
6 years ago
Solved

Fill in data in a column for same duplicate value from other column

Dear PBI Community,       I have 2 tables related to each other by IP Address column (many-to-many). In one of them I want to reflect the "support group" data, which is only in the 2nd table. So I g...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    New column in table 1

    Support Group = minx(FILTER(Table2,Table2[Hostname]=Table1[Hostname] && Table1[IP Address]=Table2[IP Address]),Table2[Support Group])
Katek10's avatar
Katek10
Frequent Visitor
6 years ago

Hello @amitchandak , 

 

   Shared you moments ago a test .pbix on OneDrive. Tried to reproduce the data set I have in my original tables - while "playing" with these test tables, I was surprised to notice some differences in the report compared with original data set: can't see listed all the IPs (although I have support group for all IPs) and I don't have blanks for duplicate IPs/hostnames. 

   Wonder whether I should do some cleanup in my source table with support groups, like creating a 3rd table with 2 columns from that table - list only entries with IPs and support groups. I simply don't get it why I get those blanks for duplicate IPs using original data sources. 

 

Cheers

Katek

Katek10's avatar
Katek10
Frequent Visitor
6 years ago

Hello, 

 

         Thought I might have done a typo somewhere in those formulas, so went back to my original data sources and did the following:

1. create new calculated column (repeated this in both table1 and table2) 

2. pasted the DAX formula: 

Support Group = minx(filter(table2,table2[Ip Address]=earlier(table2[Ip Address] && not(isblank(table2[Support

Group]))),table2[Support Group])) -> where table2 - the only table where I have the support groups. 
        Now I get the message error saying that "too many arguments were passed to FILTER function. The maximum argument count for the function is 2.". When I point on table2[Ip Address] after "earlier" it says that parameter is not the correct type and when I go on last "table2[Support Group]" it says "unexpected expression '[Support Group]'". 
 
Cheers,
K
        
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    New column in table 1

    Support Group = minx(FILTER(Table2,Table2[Hostname]=Table1[Hostname] && Table1[IP Address]=Table2[IP Address]),Table2[Support Group])
  • Katek10's avatar
    Katek10
    Frequent Visitor
    6 years ago

    Many thanks indeed Amit!

     

            It works!!! I get the expected outcome, no blanks for duplicated IPs, support groups are duplicated as many times as IPs are. Really appreciate all your time spent on this and your support!

     

    Cheers,

    K