lookupvalue for 1 instance out of many

[EF]

Hi all,

I have a facts table with records, and a client table (dimclient) with one row per client. 

I would like to add details to dimclient based on the fact table. 

Example: clients can receive different services. I want a column(ServA Clients) in dimclient: IF client ever got serviceA, then give label "ServAClient". 

Additionally, another column in dimclient that gives the date of the first time they received that service. (i. ServA start date)

I tried: IF(lookupvalue(FactTable[ServiceType],FactTable[clientID],[clientID])="ServiceA"),"ServAClient")

I got an error message: A table of multiple values was supplied where a single value was expected.

I know that there are multiple values, but I want to find if there is 1 or more (of ServA) per client.

This would probably be easier in query editor but I am using calculated columns so I need it in DAX.

[az38 · Accepted solution]

Hi @EF 

try to add columns

ServAClient = IF(
COUNTROWS(
FILTER('FactTable','FactTable'[clientID]=[clientID] && 'FactTable'[ServiceType]="ServiceA")
)>0, "ServAClient", ""
)

and

First Date = 
CALCULATE(MIN('FactTable'[StartDate]);FILTER(ALL('FactTable'); 'FactTable'[clientID]=[clientID] && 'FactTable'[ServiceType]="ServiceA"))

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[EF]

Worked perfectly!

Thank you!