Determine if any of a list of values exists in another list

[codeman71]

Hoping you Power BI masters might be able to help me out.

I have a list of Features that exist on Vehicles. I'm using a Matrix Visual to show which features are on which vehicles. It looks like this:

	Vehicle 1	Vehicle 2	Vehicle 3	Vehicle 4
+ Domain A	X		X	X
+ Domain B		X	X	X
- Domain C
Feature 1 [ID12]	X		X
Feature 2 [ID13]		X
Feature 3 [ID14]		X
+ Domain D			X	X

The problem arises when I collapse the rows into the Domains that the Features are grouped under. In my Measure for calculating the 'X', I used the MAX() function to handle when more than one Feature is selected by the visual. Here's my logic:

Feature Exists = 
var _Feature = MAX(Features[Feature ID])

return
CALCULATE(
    IF(
        _Feature in VALUES('Vehicles'[Feature ID])
        ,"X"
        )
)

So the problem is that when I collapse the Domain, Power BI only looks at the highest Feature ID and shows if it is on the vehicle. What I'd like to know is if ANY of the features on that domain are on that vehicle. In essence, I'd like to do something like:

Feature Exists = 
var _Feature = VALUES(Features[Feature ID])

But this of course causes an error in Power BI as it only wants to have a single value to look at, and not a range of values.

Is there another way I might be able to indicate if any of the features on a Domain are on a vehicle, to put the X in that column when that row is collapsed?

From my example above, when collapsing Domain C, I'd like for it to have an X for Vehicle 1, 2, and 3.

Desired Output (excerpt only):

	Vehicle 1	Vehicle 2	Vehicle 3	Vehicle 4
+ Domain C	X	X	X

My current logic output:

	Vehicle 1	Vehicle 2	Vehicle 3	Vehicle 4
+ Domain C		X

Because in Domain C with 3 features, Feature 3 has the largest ID.

Thanks so much!

[Anonymous · Accepted solution]

Hi @codeman71 ,

Here's my solution.

1.Replace your original X with 1.

Feature Exists = 
var _Feature = MAX(Features[Feature ID])
return
CALCULATE(
    IF(
        _Feature in VALUES('Vehicles'[Feature ID])
        ,1
        )
)

2.Create a new measure.

Measure = 
var _sum=SUMX('Features',[Feature Exists])
var _flag=IF(HASONEVALUE(Features[Feature Name]),[Feature Exists],_sum )
return   IF(_flag>0,"X",BLANK())

3.Here's the result.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[codeman71]

Thanks @parry2k. I created a new file that shows the issue.

Here's how the data looks in the visual:

One other oddity that results from this logic is that when you minimize some of these Domains, because the highest ID number may not be applied to a vehicle, that category just disappears. In my example, if you minimize Domain A or Domain C, they disappear from the Matrix visual, because the highest numbered item isn't applied to a vehicle.

Link to sample file

Thanks for any ideas you might have!

[parry2k]

@codeman71 interesting problem to solve, can you share sample pbix file using one drive/google drive, and remove any sensitive information before sharing.