Filter master table based on a detail table

[Anonymous]

Hi, 

I'll illustrate my issue with dumb data. 

I'm looking for a DAX code to find the number of people who have both let's say apple and strawberries. 

I tried something like but it doesn't work 😃 

test = 
CALCULATE(
    COUNT(People[People]),
    INTERSECT(
            CALCULATETABLE(
                People,
                FILTER(
                    RELATEDTABLE(Belongings),
                    'Belongings'[Fruit]=1
                )
            ),
            CALCULATETABLE(
                People,
                FILTER(
                    RELATEDTABLE(Belongings),
                    'Belongings'[Fruit]=2
                )
            )
        )
)

Any help is more than welcome, 

Thanks

[AlB]

Hi @Anonymous 

This depends on what you relationships look like but you could try this, where Table3 is the rightmost table you show:

1. Place Table3[Fruit] in a slicer and select apple and strawberries 

2. Create this measure and place it in a card visual:

Measure =
VAR NumFruits_ =
    COUNTROWS ( DISTINCT ( Table3[Fruit] ) )
RETURN
    COUNTROWS (
        FILTER (
            DISTINCT ( Table3[People] ),
            CALCULATE ( COUNT ( Table3[Fruit] ) ) = NumFruits_
        )
    )

On  a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers