Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX formula error when using Addcolumn and Groupby

 

Hi Expert

I cannot see the error here in terms of what i am doing wrong when writing the following DAX.

DAX

Concordant = 
VAR __table = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)),"__Index",RANKX(PMS_COMPLAINT,PMS_COMPLAINT[FISCAL_MON_START_DT],,,Dense))
VAR __table1 = FILTER(__table,[__Index]>EARLIER([__Index])&&[CountComplaints]>EARLIER([CountComplaints]))
VAR __count = COUNTROWS(__table1)
RETURN
IF(ISBLANK(__count),0,__count)

 

Error Message

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    7 years ago

    Hi  Anonymous ,

     

    Take try of this one:

     

    Concordant = 
VAR __table = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)),"__Index",RANKX(PMS_COMPLAINT,PMS_COMPLAINT[FISCAL_MON_START_DT],,,Dense))
VAR __count = CALCULATE(COUNTROWS(__table),FILTER(__table,[__Index]<=MAX([__Index])&&[CountComplaints]<=MAX([CountComplaints])))
RETURN
IF(ISBLANK(__count),0,__count)

     

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Experts

     

    Cannot see the wood for the trees on what i am doing wrong with the following DAX (variable formula).

    Concordant = 
VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)))
VAR __table = GROUPBY(ComplaintsByFiscalMo,"Index",RANKX(CURRENTGROUP(),[FISCAL_MON_START_DT],,,Dense))
VAR __table1 = FILTER(__table,[Index]>EARLIER([Index])&&[CountComplaints]>EARLIER([CountComplaints]))
VAR __count = COUNTROWS(__table1)
RETURN
IF(ISBLANK(__count),0,__count)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

     

    if i add ALL before filter i getting the following error.

     

    • v-diye-msft's avatar
      v-diye-msft
      Community Support
      7 years ago

      Hi  Anonymous ,

       

      Take try of this one:

       

      Concordant = 
VAR __table = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)),"__Index",RANKX(PMS_COMPLAINT,PMS_COMPLAINT[FISCAL_MON_START_DT],,,Dense))
VAR __count = CALCULATE(COUNTROWS(__table),FILTER(__table,[__Index]<=MAX([__Index])&&[CountComplaints]<=MAX([CountComplaints])))
RETURN
IF(ISBLANK(__count),0,__count)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        many thanks -Dina

         

        excellent feedback as always much appreciated.