Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dax function

Hi team, I have This Type of Data in a matricx table,My current week is falls under Q4.

My Question is that the Blank cells in the remaining Quarters should show as '0' in blank cells.

 

Can you peaople help me with the DAX function.

 

I have used this Fuction, It will not showing remaing blank cells as 0.

 

IF(ISBLANK(DISTINCTCOUNT('Append_QMR'[TOUR_REQUEST_NO])) ,0, CALCULATE(DISTINCTCOUNT('Append_QMR'[TOUR_REQUEST_NO]) ,
FILTER(ALL('Append_QMR'),
'Append_QMR'[WEEK_NO]=WEEKNUM(TODAY()) &&
'Append_QMR'[TERMINATION_YEAR]=2023 &&
'Append_QMR'[Year]=2023 &&
-----'Append_QMR'[TOUR_STATUS_KEY]=7 &&
'Append_QMR'[QT_EXCLUDE_FLG]=0 &&
'Append_QMR'[SALES_OFFICE_KEY] in VALUES(M_OFFICE_D[OFFICE_KEY]) &&
'Append_QMR'[TOUR_TYPE_KEY] IN VALUES('M_TOUR_TYPE_D'[TOUR_TYPE_KEY]) &&
'Append_QMR'[DEPARTURE_DATE_KEY] In VALUES('Depor M_DATE_D'[DATE_KEY]) &&
'Append_QMR'[TERMINATION_DATE_KEY] In VALUES('M_DATE_D'[DATE_KEY]) &&
'Append_QMR'[DEPARTURE_MONTH_KEY]>=MONTH(TODAY())
)))



  • Fowmy's avatar
    Fowmy
    2 years ago

    Anonymous 

    You can add +0 at the end of your measure :

    IF (
    ISBLANK ( DISTINCTCOUNT ( 'Append_QMR'[TOUR_REQUEST_NO] ) ),
    0,
    CALCULATE (
        DISTINCTCOUNT ( 'Append_QMR'[TOUR_REQUEST_NO] ),
        FILTER (
            ALL ( 'Append_QMR' ),
            'Append_QMR'[WEEK_NO] = WEEKNUM ( TODAY () )
                && 'Append_QMR'[TERMINATION_YEAR] = 2023
                && 'Append_QMR'[Year] = 2023
                && -----'Append_QMR'[TOUR_STATUS_KEY]=7 &&
            'Append_QMR'[QT_EXCLUDE_FLG] = 0
                && 'Append_QMR'[SALES_OFFICE_KEY]
                    IN VALUES ( M_OFFICE_D[OFFICE_KEY] )
                        && 'Append_QMR'[TOUR_TYPE_KEY]
                            IN VALUES ( 'M_TOUR_TYPE_D'[TOUR_TYPE_KEY] )
                                && 'Append_QMR'[DEPARTURE_DATE_KEY]
                                    IN VALUES ( 'Depor M_DATE_D'[DATE_KEY] )
                                        && 'Append_QMR'[TERMINATION_DATE_KEY]
                                            IN VALUES ( 'M_DATE_D'[DATE_KEY] )
                                                && 'Append_QMR'[DEPARTURE_MONTH_KEY] >= MONTH ( TODAY () )
        )
    )
) + 0



  • Anonymous's avatar
    Anonymous
    2 years ago

    Iam Done with that

    but I Want Another solution ..please hep me

     

6 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Anonymous 

    You can add +0 at the end of your measure :

    IF (
    ISBLANK ( DISTINCTCOUNT ( 'Append_QMR'[TOUR_REQUEST_NO] ) ),
    0,
    CALCULATE (
        DISTINCTCOUNT ( 'Append_QMR'[TOUR_REQUEST_NO] ),
        FILTER (
            ALL ( 'Append_QMR' ),
            'Append_QMR'[WEEK_NO] = WEEKNUM ( TODAY () )
                && 'Append_QMR'[TERMINATION_YEAR] = 2023
                && 'Append_QMR'[Year] = 2023
                && -----'Append_QMR'[TOUR_STATUS_KEY]=7 &&
            'Append_QMR'[QT_EXCLUDE_FLG] = 0
                && 'Append_QMR'[SALES_OFFICE_KEY]
                    IN VALUES ( M_OFFICE_D[OFFICE_KEY] )
                        && 'Append_QMR'[TOUR_TYPE_KEY]
                            IN VALUES ( 'M_TOUR_TYPE_D'[TOUR_TYPE_KEY] )
                                && 'Append_QMR'[DEPARTURE_DATE_KEY]
                                    IN VALUES ( 'Depor M_DATE_D'[DATE_KEY] )
                                        && 'Append_QMR'[TERMINATION_DATE_KEY]
                                            IN VALUES ( 'M_DATE_D'[DATE_KEY] )
                                                && 'Append_QMR'[DEPARTURE_MONTH_KEY] >= MONTH ( TODAY () )
        )
    )
) + 0



    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you for the help.But when compare to current week count with last week count,It is showing like this

      But I want to repesent it like current week and last week and variation in Totals only.How can I do that.

       

       

      please help

       

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        Anonymous 

        You can modify the measure to check for current week and last week the apply +0 accordingly