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

[Katek10]

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 get the correct input, however I wonder whether I could do something to have confirmed support group per IP Address filled in for all duplicate entries. 

    The reason why I have duplicate entries, as shown in the sample table, is because for each IP/server there are multiple changes with a different implementation status (not sure if relevant, each change may be valid for several servers e.g. change no. may 1 apply to server 2,3,4 etc).

Many thanks in advance for your feedback/support!

[amitchandak]

Try

support Group = minx(filter(table,table[ip]=earlier(table[ip]) && not(isblank(table[support Group]))),table[support Group])

Or
support Group = minx(filter(table,table[ip]=earlier(table[ip]) && not(isblank(earlier(table[support Group])))),table[support Group])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Katek10]

Hello @amitchandak

      I get the error message below: 

A single value for column "IP Address" in table1
cannot be determined. This can happen when a measure
formula refers to a column that contains many values
without specifying an aggregation such as min, max, count,
or sum to get a single result.

* indeed, in both tables there might be empty values in IP Address columns, or an IP address can be found only in one of them. Don't care whether the data based on which I've related those 2 tables is accurate/complete, the gap is acceptable for me.

What I want is - at least for the IPs which I have in both tables and for those for which in table 2 I have confirmed the support group - to be able to count how many servers or IP addresses per support group are in each implementation status. And currently, I can't precisely count that due to blank fields from column Support Group. 

Best regards,

Catalin

[amitchandak]

create it as a new column.

[Katek10]

Hello @amitchandak, 

          That's exactly what I tried to do - create a calculated column with those DAX formulas, in both cases retrieved the same error. 

Cheers

[amitchandak]

Can you share sample data. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Katek10]

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]

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 · Accepted solution]

New column in table 1

Support Group = minx(FILTER(Table2,Table2[Hostname]=Table1[Hostname] && Table1[IP Address]=Table2[IP Address]),Table2[Support Group])

[Katek10]

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