Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Addcolumns DAX formula error

Hi Experts

 

cannot see the wood for the trees, i just want to rank my values based on the data column.

Table 2 = ADDCOLUMNS(ADDCOLUMNS(FILTER(CALENDAR(MIN(PMS_FINANCIAL_PDS[FISCAL_MON_START_DT]),MAX(PMS_FINANCIAL_PDS[FISCAL_MON_END_DT])),DAY([Date]) =1),
        "CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT),
            FILTER(PMS_COMPLAINT,PMS_COMPLAINT[Fiscal_Mnth_Start_Date].[Year] = YEAR(EARLIER([Date]))
                && PMS_COMPLAINT[Fiscal_Mnth_Start_Date].[MonthNo] = MONTH(EARLIER([Date]))))+0),"__Index",RANKX(PMS_COMPLAINT,PMS_COMPLAINT[Fiscal_Mnth_Start_Date],,,Dense))
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Microsoft Team i have managed to solve said solution

     

    VAR Mytable1 = FILTER(ADDCOLUMNS(VALUES(PMS_FINANCIAL_PDS[Month Start]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)),"__Index",RANKX(PMS_FINANCIAL_PDS,PMS_FINANCIAL_PDS[Month Start],,,Dense )),[Month Start] <> BLANK ()
)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    can you upload a sample with your expect outcome?  I'm pretty sure at least some of the error has to do with 

    Table 2 =
ADDCOLUMNS (
    ADDCOLUMNS (
        FILTER (
            CALENDAR (
                MIN ( PMS_FINANCIAL_PDS[FISCAL_MON_START_DT] ),
                MAX ( PMS_FINANCIAL_PDS[FISCAL_MON_END_DT] )
            ),
            DAY ( [Date] ) = 1
        ),
        "CountComplaints", CALCULATE (
            COUNTROWS ( PMS_COMPLAINT ),
            FILTER (
                PMS_COMPLAINT,
                PMS_COMPLAINT[Fiscal_Mnth_Start_Date].[Year] = YEAR ( EARLIER ( [Date] ) )
                    && PMS_COMPLAINT[Fiscal_Mnth_Start_Date].[MonthNo] = MONTH ( EARLIER ( [Date] ) )
            )
        ) + 0
    ),
    "__Index", RANKX ( PMS_COMPLAINT, PMS_COMPLAINT[Fiscal_Mnth_Start_Date],,, DENSE )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Nick

       

      The expected end results need to be... see image

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

    Hi Anonymous ,

     

    Do you have some sample data or preferably a sample pbix file?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Microsoft Team i have managed to solve said solution

       

      VAR Mytable1 = FILTER(ADDCOLUMNS(VALUES(PMS_FINANCIAL_PDS[Month Start]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)),"__Index",RANKX(PMS_FINANCIAL_PDS,PMS_FINANCIAL_PDS[Month Start],,,Dense )),[Month Start] <> BLANK ()
)