Check if one of many rows contains value

[PeterStuhr]

Hi all,

We have a Sales Table containing

JobID, ServiceTypeID

An example could be:

JobID	ServiceID
1	1
1	2
2	1
2	3

If I want to mark the JobIDs that includes ServiceID 3, how would I do that?

I do not just want to filter for ServiceID = 3, as I want to see all Services included for the JobID.

So in the above example, JobID 2 contains ServiceID 1 and 3. I would love to see both in a table.

So ideally, my output would be something like:

JobID	ServiceID	Marked
1	1	FALSE
1	2	FALSE
2	1	TRUE
2	3	TRUE

[v-jingzhang · Accepted solution]

Hi @PeterStuhr 

Try this measure

Measure = 
VAR _JobIDs = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[ServiceID]=3),"Job_ID",[JobID]))
RETURN
IF(SELECTEDVALUE('Table'[JobID]) IN _JobIDs, "True", "False")

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[MrHecht]

Thanks for this answer, 

I was able to apply it for my problem.

I would like to compare if the one Service-ID is part of a list of Job IDs (around 700) but the performence of this measure is very slow. Do you have any idea how I can improve it because the data set I use is a bigger one. 

Best 

Felix 

[CNENFRNL]

[Fowmy]

@PeterStuhr 

To achieve this, first, you need to create a disconnected table. Click on New Table under Modeling > New Table.

You can find PBIX file below my signature

Create the following measure which will get the selected service ID from the new table and identify each row that falls under the same job id. Then assign it to the visual filter and set it equal to 1.

Service Selected = 

var __serviceselected = SELECTEDVALUE('Service Slicer'[ServiceID]) return
var __commonjobid = CALCULATE(MAX(Table1[JobID]), Table1[ServiceID] = __serviceselected)
return

    INT(HASONEVALUE(Table1[JobID]) && MAX(Table1[JobID]) = __commonjobid)