Forum Discussion

mattmickeyj's avatar
mattmickeyj
Frequent Visitor
3 years ago
Solved

Filter by 2 columns

Hi,   I may be overthinking this, but I have a table as follows:   Student ID Subject Grade ABC1 Maths A ABC1 English B ABC1 Science A ABC2 Maths B ABC2 English A ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi mattmickeyj 

    please try

    A_in_all_Subjects =
    SUMX (
    VALUES ( 'RESULTS'[Student ID] ),
    VAR T1 =
    CALCULATETABLE ( 'RESULTS' )
    VAR T2 =
    FILTER ( T1, 'RESULTS'[SUBJECT] IN { "Maths", "English", "Science" } )
    VAR T3 =
    FILTER ( T2, 'RESULTS'[GRADE] IN { "A", "B" } )
    RETURN
    INT ( COUNTROWS ( T2 ) = COUNTROWS ( T3 ) )
    )

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi mattmickeyj 

please try

A_in_all_Subjects =
SUMX (
VALUES ( 'RESULTS'[Student ID] ),
VAR T1 =
CALCULATETABLE ( 'RESULTS' )
VAR T2 =
FILTER ( T1, 'RESULTS'[SUBJECT] IN { "Maths", "English", "Science" } )
VAR T3 =
FILTER ( T2, 'RESULTS'[GRADE] IN { "A", "B" } )
RETURN
INT ( COUNTROWS ( T2 ) = COUNTROWS ( T3 ) )
)

mattmickeyj's avatar
mattmickeyj
Frequent Visitor
3 years ago

A little follow up, sorry - if I wanted to then add another filter say to filter it by a test date.  If I have a column called 'test_period' could I then make it into something like this?

 

A_in_all_Subjects =
SUMX (
VALUES ( 'RESULTS'[Student ID] ),
VAR T1 =
CALCULATETABLE ( 'RESULTS' )
VAR T2 =
FILTER ( T1, 'RESULTS'[SUBJECT] IN { "Maths", "English", "Science" } )
VAR T3 =
FILTER ( T2, 'RESULTS'[GRADE] IN { "A", "B" } )

VAR T$ =
FILTER ( T3, 'RESULTS'[TEST_PERIOD] ="Spring")
RETURN
INT ( COUNTROWS ( T2 ) = COUNTROWS ( T3 )  = COUNTROWS (T4) )
)

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    mattmickeyj 

    Yes you can but I would rather leave the test period in the outer filter either as a slicer or part of the visual. In case you want to aggregate the count over multiple selected periods you may use

    A_in_all_Subjects =
    SUMX (
    SUMMARIZE ( 'RESULTS', 'RESULTS'[Student ID], 'RESULTS'[TEST_PEEIOD] ),
    VAR T1 =
    CALCULATETABLE ( 'RESULTS' )
    VAR T2 =
    FILTER ( T1, 'RESULTS'[SUBJECT] IN { "Maths", "English", "Science" } )
    VAR T3 =
    FILTER ( T2, 'RESULTS'[GRADE] IN { "A", "B" } )
    RETURN
    INT ( COUNTROWS ( T2 ) = COUNTROWS ( T3 ) )
    )

    • mattmickeyj's avatar
      mattmickeyj
      Frequent Visitor
      3 years ago

      Thank you, you knowledge is ace.  The use case for wanting to create a measure for just spring for example is to then be able to compare autumn to spring (e.g. increase of 5% between autumn and spring). Would this be achieved by adding an extra variable to query to filter for spring and then another for autumn so that it could then be displayed in a table.