topic Re: Filtering data in DAX with a related table in DAX Commands and Tips

Filtering data in DAX with a related table

KrisIsLearning — Tue, 14 Nov 2023 14:46:37 GMT

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

Re: Filtering data in DAX with a related table

FreemanZ — Tue, 14 Nov 2023 14:12:33 GMT

hi KrisIsLearning ,

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

Re: Filtering data in DAX with a related table

KrisIsLearning — Tue, 14 Nov 2023 14:58:58 GMT

I updated the original post.

Re: Filtering data in DAX with a related table

KrisIsLearning — Tue, 14 Nov 2023 17:35:27 GMT

I'm getting into the correct direction:

CONCATENATEX(

FILTER(

'Reports',

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

)

'Reports'[Rubriek],

", ",

'Reports'[Rubriek],

ASC

)