Calculated measure DAX function

Hi Anonymous ,

Based on your description, it appears that you are trying to calculate different counts of the number of weekly travel applications, taking into account various conditions. Let us solve your problem step-by-step.

1. The ALLEXCEPT function removes all contextual filters from the table except those applied to the specified columns. Double check that this is consistent with your model logic.

2. Your current filters do not appear to be correct. the VALUES function returns a single list of different values for the specified column. but you are comparing [WEEK_NO] and [DATE_OF_TRANSFER]. [DAY], which may not be compatible. You should ensure that you are comparing equivalent and related date components.

3. Break the measure down into simpler parts and test each condition separately to ensure they work as expected. This helps to identify where the logic may be failing.

Adjusted Dax Code Example:

INQ_TOUR_REQUEST_NO-2024 =
CALCULATE (
    DISTINCTCOUNT ( 'Append-QMR'[TOUR_REQUEST_NO] ),
    FILTER (
        'Append-QMR',
        'Append-QMR'[Year] = YEAR ( TODAY () )
            && 'Append-QMR'[WEEK_NO] = MAX ( 'Append-QMR'[WEEK_NO] )
            && 'Append-QMR'[TOUR_TYPE_KEY]
                IN VALUES ( M_TOUR_TYPE_D[TOUR_TYPE_KEY] )
                    && 'Append-QMR'[DESTINATION_COUNTRY_KEY]
                        IN VALUES ( M_COUNTRY_D[COUNTRY_KEY] )
                            && 'Append-QMR'[TERMINATION_DATE_KEY] IN VALUES ( M_DATE_D[DATE_KEY] )
    ),
    ALLEXCEPT ( 'M_DATE_WEEK_D', 'M_DATE_WEEK_D'[WEEK_NO] )
)

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.