Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Condition on column in GROUPBY

Hi, 
I have a measure:

 

VAR calcTable = CALCULATETABLE(
GROUPBY(
'Certification History';
'Certification History'[ID Entity-account];
"Rejections"; SUMX(CURRENTGROUP(); 1)
);
FILTER(
ALLEXCEPT('Certification History'; 'Certification History'[ID Entity-account]; 'Certification History'[Report Date]);
'Certification History'[Status] = "Rejected"
)
)
RETURN
IF(
ISFILTERED('Certification History'[ID Entity-account]);
SUMX(calcTable; [Rejections]);
COUNTROWS(calcTable)
)
 
and I need it to return either SUM or COUNT of 'calcTable'[Rejections] >= {some value}

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    So, my final working solution is:

    VAR calcTable = CALCULATETABLE(
    GROUPBY(
    'Certification History';
    'Certification History'[ID Entity-account];
    "Rejections"; SUMX(CURRENTGROUP(); 1)
    );
    FILTER(
    ALLEXCEPT('Certification History'; 'Certification History'[ID Entity-account]; 'Certification History'[Report Date]);
    'Certification History'[Status] = "Rejected"
    )
    )
    VAR limit = SELECTEDVALUE('How many is multiple?'[How many is multiple?])
    RETURN
    IF(
    ISFILTERED('Certification History'[ID Entity-account]);
    SUMX(FILTER(calcTable; [Rejections] >= limit); [Rejections]);
    COUNTAX(FILTER(calcTable; [Rejections] >= limit); 1)
    )
     
    The part in red is what was missing. Someone named CR7SMS helped me on stackoverflow so all the credits go to him.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Try like

    sumx(summarize(filter(ALLEXCEPT('Certification History'; 'Certification History'[ID Entity-account]; 'Certification History'[Report Date]);
'Certification History'[Status] = "Rejected"
)'Certification History'[ID Entity-account];
"Rejections"; SUMX(CURRENTGROUP(); 1)),if([Rejections]>{some value},blank(),[Rejections]))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      So, my final working solution is:

      VAR calcTable = CALCULATETABLE(
      GROUPBY(
      'Certification History';
      'Certification History'[ID Entity-account];
      "Rejections"; SUMX(CURRENTGROUP(); 1)
      );
      FILTER(
      ALLEXCEPT('Certification History'; 'Certification History'[ID Entity-account]; 'Certification History'[Report Date]);
      'Certification History'[Status] = "Rejected"
      )
      )
      VAR limit = SELECTEDVALUE('How many is multiple?'[How many is multiple?])
      RETURN
      IF(
      ISFILTERED('Certification History'[ID Entity-account]);
      SUMX(FILTER(calcTable; [Rejections] >= limit); [Rejections]);
      COUNTAX(FILTER(calcTable; [Rejections] >= limit); 1)
      )
       
      The part in red is what was missing. Someone named CR7SMS helped me on stackoverflow so all the credits go to him.

       

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Please try this formula. If it doesn't meet your requirement,  kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

     

    a =
VAR somevalue = 2
VAR calcTable =
    CALCULATETABLE (
        GROUPBY (
            'Certification History';
            'Certification History'[ID Entity-account];
            "Rejections"; SUMX ( CURRENTGROUP (); 1 )
        );
        FILTER (
            ALLEXCEPT (
                'Certification History';
                'Certification History'[ID Entity-account];
                'Certification History'[Report Date]
            );
            'Certification History'[Status] = "Rejected"
        )
    )
VAR k =
    FILTER ( calcTable; [Rejections] > somevalue )
RETURN
    SUMX ( k; [Rejections] )