Forum Discussion

KrisIsLearning's avatar
KrisIsLearning
Frequent Visitor
2 years ago
Solved

Filtering data in DAX with a related table

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

aardok
wgnrnok
testok

 

and Reports:

IdRubriek

1aard
1wgnr
1bla
2aard

 

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:
 
IdCountOkCountNokCountUnknownListOkListNokListUnknown
1111aardwgnrbla
2100aard  
  • KrisIsLearning's avatar
    KrisIsLearning
    2 years ago

    I'm getting into the correct direction:

     

            CONCATENATEX(
                FILTER(
                    'Reports',
                    'Reports'[Rubriek] = RELATED(KnownRubrics[Rubriek])
                )
                ,
                'Reports'[Rubriek],
                ", ",
                'Reports'[Rubriek],
                ASC
            )

3 Replies

  • KrisIsLearning's avatar
    KrisIsLearning
    Frequent Visitor
    2 years ago

    I'm getting into the correct direction:

     

            CONCATENATEX(
                FILTER(
                    'Reports',
                    'Reports'[Rubriek] = RELATED(KnownRubrics[Rubriek])
                )
                ,
                'Reports'[Rubriek],
                ", ",
                'Reports'[Rubriek],
                ASC
            )