Forum Discussion

ConnollyZ's avatar
ConnollyZ
Frequent Visitor
4 years ago
Solved

RowCount with multiple conditions

Im struggling to undestand the dax to count cross rows with multiple conditions. I dont really know what the termonology is to search the answer effectively.

 

I would appericate any support with the answer

 

For Reading, Writing and Maths.

  • Count of grades where it is GDS for all subjects. - example answer will = 1
  • Count of grade where it is EXP or GDS for all subjects. - example answer will = 2

https://1drv.ms/u/s!AmEktzChYLvAtADTKsDaqceW532Z?e=rpeLtL

 

thanks,

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi ConnollyZ 
    Here is the sample file with the solution https://www.dropbox.com/t/jYYoXx4gjMHPYC9V

    GDS Only = 
SUMX (
    VALUES ( Grades[Name] ),
    VAR CurrentNameGrades = 
        CALCULATETABLE ( VALUES ( Grades[Grade] ), ALLEXCEPT ( Grades, Grades[Name] ) )
    RETURN
        IF ( 
            COUNTROWS ( EXCEPT ( CurrentNameGrades, { "GDS" } ) ) = 0,
            1
        )
)
    EXP or GDS = 
SUMX (
    VALUES ( Grades[Name] ),
    VAR CurrentNameGrades = 
    CALCULATETABLE ( VALUES ( Grades[Grade] ), ALLEXCEPT ( Grades, Grades[Name] ) )
    RETURN
        IF ( 
            COUNTROWS ( EXCEPT ( CurrentNameGrades, { "EXP", "GDS" } ) ) = 0,
            1
        )
)

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi ConnollyZ 
    Here is the sample file with the solution https://www.dropbox.com/t/jYYoXx4gjMHPYC9V

    GDS Only = 
SUMX (
    VALUES ( Grades[Name] ),
    VAR CurrentNameGrades = 
        CALCULATETABLE ( VALUES ( Grades[Grade] ), ALLEXCEPT ( Grades, Grades[Name] ) )
    RETURN
        IF ( 
            COUNTROWS ( EXCEPT ( CurrentNameGrades, { "GDS" } ) ) = 0,
            1
        )
)
    EXP or GDS = 
SUMX (
    VALUES ( Grades[Name] ),
    VAR CurrentNameGrades = 
    CALCULATETABLE ( VALUES ( Grades[Grade] ), ALLEXCEPT ( Grades, Grades[Name] ) )
    RETURN
        IF ( 
            COUNTROWS ( EXCEPT ( CurrentNameGrades, { "EXP", "GDS" } ) ) = 0,
            1
        )
)
  • ConnollyZ's avatar
    ConnollyZ
    Frequent Visitor
    4 years ago

    thank you for this! Seeing the solution I wouldnt have worked it out.

    Would you be able offer an explaination for how the dax works please?

     

    thank you again.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    ConnollyZ 
    Fist of all the subject is to count the number of names that fulfill the given conditions. VALUES provides a one column table that contains the distinct values of Names and that is the required granuraity to start with. This is the table that we need to filter and then count it's rows. There are two possible ways to do that: either using FILTER and wrap it with COUNTROWS or using SUMX to count using the simple method IF TRUE then 1 Otherwise do nothing (like a counter) and this is the method I followed.
    In both cases iteration over to correct granularity table is required. Then using CALCULATETABLE we are creating a filter context that retrieves the VALUES of Grade associated with each name. ALLEXCEPT is a CALCULATE/CALCULATETABLE modifier that makes sure while creating this new filter context all filters from all columns other than "Name" are removed (for example in the table from your example we have a filter coming the the column "Grade" itself and this filter will affect the results and need to be removed. In the case of the card visual that won't make any difference as there is no existing filter context in cards). 
    Now we are able to obtain a table of all the Grades associated with the current Name under iteration that we just need to compare with our conditions table. I chose EXCEPT function for this task to check: if the condition values are removed from the calculated table does anything else remains inside it? If not (COUNTROWS of the resulted table is 0) then count 1. Then move to the next name and repeat..