Filtering data in DAX with a related table

[KrisIsLearning]

I know how to do it with M but want to get more into DAX syntax but I need a few hints from the community.

I've got 2 tables set up:

KnownRubrics

RubriekCondition

aard	ok
wgnr	nok
test	ok

and Reports:

IdRubriek

1	aard
1	wgnr
1	bla
2	aard

I created already several quick measures like the following:

Count of Rubriek for ok =

CALCULATE(

    COUNTA('Reports'[Rubriek]),

    'KnownRubrics'[Condition] IN { "ok" }

)

and 

List of Rubriek values =

        CONCATENATEX(

            VALUES('Reports'[Rubriek]),

            'Reports'[Rubriek],

            ", ",

            'Reports'[Rubriek],

            ASC

        )

What I'm after is 3 columns that give me the concatenated string when in the related table it's an ok and another table with all the concatenated text when it's nok. Also another one when there's no corresponding value in the KnownRubrics table.

Expected result:

Id	CountOk	CountNok	CountUnknown	ListOk	ListNok	ListUnknown
1	1	1	1	aard	wgnr	bla
2	1	0	0	aard

[KrisIsLearning · Accepted solution]

I'm getting into the correct direction:

        CONCATENATEX(

            FILTER(

                'Reports',

                'Reports'[Rubriek] = RELATED(KnownRubrics[Rubriek])

            )

            ,

            'Reports'[Rubriek],

            ", ",

            'Reports'[Rubriek],

            ASC

        )

[FreemanZ]

hi @KrisIsLearning ,

could you provide the expected result as a table as well?

[KrisIsLearning]

I updated the original post.