Help with DAX measures with text not number

[Anonymous]

Hello,

I am a network engineer trying to get my head around using Measures with DAX.

Simply put, I have a list of IP Addresses I am trying to find the Owners and Hostnames for. It has some duplicates in it.

I have been given a list of IP/Hostname/Owners in an Excel spreadsheet.

My list:

IP List	Comment
10.1.1.1	Patched
10.1.1.2	New
10.1.1.3	Commissioned
10.1.1.3	Patched
10.1.1.5	New

Given List:

IP List	HostName	Owner
10.1.1.1	Mail	Tom
10.1.1.3	DC	**bleep**
10.1.1.4	Test	Harry

I would like to create a matrix/table that automatically checks if the IP addresses I have are in the provided table, and if so,

1: Tell me who the Owner is
2: Tell me what the Hostname is.

3: How many times the IP address is listed in my original table.

I have tried to do this with measures, but I am having trouble finding good examples of how to do measures with Text values. There are lots of examples with dates and numbers.

Would anybody be able to give me a hint?

[tamerj1 · Accepted solution]

@Anonymous 

Based on your description I can understand that the 'Given List' is the lookup (dimension) table. You can create a relationship between the two tables baes on IP were 'Given List' filters 'My List'. 
Insert a table visual and drag the three columns from 'Given List' into it and make sure to select "Don't summarize" for all of them. 
tou can create a measure called [Comments] which would be

Comments =
CONCATENATEX ( 'My List', 'My List'[comment], UNICHAR ( 10 ) )

Then another measure [# of Comments]

# of Comments =
COUNTROWS ( 'My List' )

[amitchandak]

@Anonymous , You can join table 1 and Table 2 In data model View on column IP List . Table 2 seems like a master.

Nor countrows(Table1) or distinctcount(Table[IP List])  can be used and plot with columns of table 2

[Anonymous]

Amit,

Thank you for your thoughts, I am sure that we could do it that way. Perhaps I over simplified what I was doing.
I am trying to use measures because a) I have been told that it it a good way to go and b) I have multiple large data sets and would like to dynamically create some reporting.

Here is a more specific example, with just a few results.

The fields 'IP Addresses', 'Message Count','SME' and 'Last Check' are from my table 1, imported from a large Excel worksheet.

I am looking for the area that under which the administration of that device is conducted.

There are a number of Worksheets in a smaller Workbook that I reference via Measures that have something like 'CALCULATE(max('Server'[IP])) <> "" ' to get a TRUE() or FALSE() value if to see if the address exists in the WorkSheet.
I have a final Measure that based on the location I find a positive result, looks up the 'Function' name (e.g. 'Storage').

The problem I am trying to resolve is as follows. 

If I omit the three columns from my table 1,  'Message Count','SME' and 'Last Check' , I only get the results which have returned a 'Function'. These are the only results I want to see.
When I add these three columns 'Message Count','SME' and 'Last Check' , I get the rest of the IP addresses in my large table listed too, as you can see with the example empty entries in the table below.

The page and Table/Matrix filters will not let me select say 'Not Blank' for a Measure, they will not let me select anything.

I have filtered the results to show me only 'Unknown' in 'SME', but that is not a measure, that is in the same line as the 'IP Address' in my Table 1.

The question is : How do I get the table to only show entries that the Measure has found an 'Function' for?

IP Addresses	Function	SME	Message Count	Last Check
10.26.124.176	Storage	Unknown	1	27/08/21
10.26.124.177	Storage	Unknown	1	26/08/21
10.27.124.176	Storage	Unknown	1	28/09/21
192.200.192.130	Server	Unknown	4	30/09/21
192.200.192.131	Server	Unknown	4	28/09/21
192.200.192.133	Server	Unknown	4	2/10/21
10.26.212.12	Server	Unknown	1	28/09/21
10.26.217.161	Server	Unknown	7	25/09/21
10.26.27.61	Server	Unknown	5	29/09/21
10.28.213.68	Server	Unknown	1	19/09/21
192.200.214.90	ESXi	Unknown	1	17/09/21
192.200.214.93	ESXi	Unknown	1	17/09/21
192.201.214.93	ESXi	Unknown	1	16/09/21
172.54.195.80	ESXi	Unknown	1	27/09/21
172.54.195.84	ESXi	Unknown	1	28/09/21
172.54.195.86	ESXi	Unknown	1	27/09/21
172.104.100.200		Unknown	2	29/09/21
172.104.12.249		Unknown	1	28/08/21
172.104.12.250		Unknown	2	28/09/21
172.104.108.200		Unknown	2	1/10/21

I hope that clarifies the picture a little.
I could just right something in powershell to sort the data for me, but I am new to Power BI and am using this as an excercise. It would be nicer to be able to do this in Power BI.

Regards,

Wolf