topic Re: Related with IF Statement in DAX Commands and Tips

Related with IF Statement

Anonymous — Thu, 23 Nov 2023 14:02:03 GMT

Hi ,

I have 2 related tables (*-1):

Table A

Host	Device	IP
Host1	vmk0	IP1
Host2	vmk0	IP2
Host3	vmk0	IP3
Host4	vmk0	IP4
Host5	vmk0	IP5
Host6	vmk0	IP6
Host7	vmk0	IP7
Host8	vmk0	IP8
Host9	vmk0	IP9
Host1	vmk1	IP10
Host2	vmk1	IP11
Host3	vmk1	IP12
Host4	vmk1	IP13
Host5	vmk1	IP14
Host6	vmk1	IP15
Host7	vmk1	IP16
Host8	vmk1	IP17
Host9	vmk1	IP18
Host10	vmk2	IP19
Host11	vmk3	IP20
Host12	vmk4	IP21
Host13	vmk5	IP22
Host14	vmk6	IP23

Table B

Host

Host1

Host2

Host3

Host4

Host5

Host6

Host7

Host8

Host9

I would like to add the table B two columns :

Table B (result)

Host	IP VMK0	IP VMK1
Host1	IP1	IP10
Host2	IP2	IP11
Host3	IP3	IP12
Host4	IP4	IP13
Host5	IP5	IP14
Host6	IP6	IP15
Host7	IP7	IP16
Host8	IP8	IP17
Host9	IP9	IP18

IP VMK0 = IF(TableA[Device] = "vmk0", TableA[IP])

IP VMK1 = IF(TableA[Device] = "vmk1", TableA[IP])

Thank you in advance for your help.

Re: Related with IF Statement

ERD — Thu, 23 Nov 2023 14:20:05 GMT

Hi Anonymous ,

If you need a calculated column, here is one of the ways:

IP VMK0 = VAR IPVMK = "vmk0" VAR t = FILTER ( TableA, TableA[Device] = IPVMK && TableA[Host] = TableB[Host] ) RETURN MAXX ( t, TableA[IP] )

IP VMK1 = VAR IPVMK = "vmk1" VAR t = FILTER ( TableA, TableA[Device] = IPVMK && TableA[Host] = TableB[Host] ) RETURN MAXX ( t, TableA[IP] )

Re: Related with IF Statement

ERD — Thu, 23 Nov 2023 14:22:22 GMT

Or, if it is to be a measure:

IP VMK0 measure = VAR IPVMK = "vmk0" VAR t = FILTER ( TableA, TableA[Device] = IPVMK && TableA[Host] = SELECTEDVALUE(TableB[Host]) ) RETURN MAXX ( t, TableA[IP] )

Re: Related with IF Statement

Anonymous — Thu, 23 Nov 2023 14:29:15 GMT

Thank you for your prompt reply.