DAX - filtering the result of group by function

[nadavsn]

Hi all

I have a dataset (named IMP_UGE_DATA) with 2 relevant columns:  IMP_UGE_DATA[FAILED] which is a text represent an error code and  IMP_UGE_DATA[Total] which always have the value 1. 

I create a measure that counts the number of rows with the most frequent FAILED :

maxFailureCount =
VAR CountByFailureCode =
    GROUPBY (
        IMP_UGE_DATA,
        IMP_UGE_DATA[FAILED],
        "countFail", SUMX ( CURRENTGROUP (), 'IMP_UGE_DATA'[Total] )
    )
RETURN
    MAXX ( CountByFailureCode, [countFail] )

Now I want to create a new measure that returns the most frequent FAILED.

I tried to use the CountByFailureCode since it supposes to return a table but I can not get the FAILED column. 

how can I see the columns of the CountByFailureCode? 

any suggestion how can I get the most frequent FAILED? 

Thank you in advance for any help attempt. 

[Anonymous]

Perhaps you can create a calculated table like this:

FailCountTable =
VAR CountByFailureCode =
    SUMMARIZE (
        'IMP_UGE_DATA';
        IMP_UGE_DATA[FAILED];
        "countFail"; COUNTROWS ( 'IMP_UGE_DATA' )
    )
RETURN
    ADDCOLUMNS (
        CountByFailureCode;
        "Rank"; RANKX ( CountByFailureCode; [countFail];; DESC )
    )

Then filter on Rank = 1 to get the top error code/codes.
I changed to SUMMARIZE and COUNTROWS instead of GROUPBY and SUMX. Then you don't need the Total column.

[nadavsn]

@Anonymous , @Greg_Deckler Thank you 

I tried the following DAX: 

FailCountTable =

VAR CountByFailureCode =

SUMMARIZE (

'IMP_UGE_DATA',

[FAILED],

"countFail", COUNTROWS ( 'IMP_UGE_DATA' )

)

var RankFailure =

ADDCOLUMNS (

CountByFailureCode,

"Rank", RANKX ( CountByFailureCode,[countFail])

)

return CALCULATE(filter(RankFailure, [Rank]=1),RankFailure[FAILED])

but I get an error "Cannot find table 'RankFailure'."

How can I approach to a column in a table var? what am I missing? 

[Anonymous · Accepted solution]

Hi! Referencing columns in table variables is quite hard in DAX for some reason. But some iterator functions can do it. So if you don't want to use a calculated table for this, try someting like this:

MaxFailed =
VAR CountByFailureCode =
    SUMMARIZE (
        'IMP_UGE_DATA',
        [FAILED],
        "countFail", COUNTROWS ( 'IMP_UGE_DATA' )
    )
VAR RankFailure =
    ADDCOLUMNS (
        CountByFailureCode,
        "Rank", RANKX ( CountByFailureCode, [countFail] )
    )
RETURN
    CALCULATE ( MAXX ( FILTER ( RankFailure, [Rank] = 1 ), [FAILED] ) )

[nadavsn]

It does work. 

I did not understand why we needed the maxx when we only have one filtered row.

Is it a workaround solution to return the column?

[Anonymous]

It's a workaround because the X funktions can access columns like that in variables. Also notice that if two or more codes have the same number of failures, the max (in alphabetical order) will be returned. MIN the other way around...

I don't know why it's not possible to write for example VALUES(Variable[Column]). Perhaps just not implemented yet in DAX.

[Greg_Deckler]

Just create a table and return CountByFailureCode as the output. I think generally you would perhaps use ADDCOLUMNS to add a RANKX.