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]),"Co...
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)

     

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

What's the error you are getting? I'm going to guess that the issue is your use of EARLIER. EARLIER references the previous context, which doesnt look like you have one, but just a guess w/o knowing the error and seeing the data. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Nick_M

     

    That is correct. Earlier , earlier...as mentioned in your reply.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      you need to tell earlier what to reference. Can try wrapping an ALL around the table and see if that would work. But would be easier if you can upload some sample data with the required output. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        hang fire uploading sample data and required output.