Calculated measure DAX function

[DanduMani104]

Hello team, Need help

Iam  getting the difficult to create this dax function from the last week.
I have a table called Append-QMR in power bi report. For calculating Distinctcount of tour request numbers between week days for each week ..I have used below DAX measure

INQ_TOUR_REQUEST_NO-2024 = CALCULATE(DISTINCTCOUNT('Append-QMR'[TOUR_REQUEST_NO]),

ALLEXCEPT('M_DATE_WEEK_D','M_DATE_WEEK_D'[WEEK_NO]) ,

'Append-QMR'[WEEK_NO]=MAX('Append-QMR'[WEEK_NO]),

'Append-QMR'[WEEK_NO] in VALUES(M_DATE_WEEK_D[DATE_OF_TRANSFER].[Day]),

'M_DATE_WEEK_D'[YEAR_OF_WEEK]=YEAR(TODAY()),

'Append-QMR'[Year]=YEAR(TODAY()),

'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]),

'M_INQ_DATE_D'[FULL_DATE] in VALUES(M_DATE_WEEK_D[DATE_OF_TRANSFER].[Date]),

'Append-QMR'[INQUIRY_DATE_KEY] in VALUES(M_INQ_DATE_D[DATE_KEY])

)
But Iam getting wrong values.
same logic I have built in Database "select COUNT(DISTINCT TOUR_REQUEST_NO) from s_spl_qmr_do_weekly_2024_f join m_date_d on INQUIRY_DATE_KEY = date_key where YEAR =2024 and FULL_DATE in (SELECT DATE_OF_TRANSFER from "DBA".M_DATE_WEEK_D where WEEK_NO= DATEPART(WEEK, GETDATE())-1)"

and in this INQUIRY_DATE_KEY from Append-QMR is related with DATE_KEY in M_DATE_D table..in M_DATE_D table we have FULL_DATE for every INQUIRY_DATE_KEY. and FULL_DATE column is related with DATE_OF_TRANSFER column in M_DATE_WEEK_D table by 1-1 relationship...This is the model.

for 8th week it should be 452 count.

this is my model

[v-yifanw-msft]

Hi @DanduMani104 ,

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.

[DanduMani104]

Thanks for this, But How can I Break the measure down into simpler parts .

[lbendlin]

Use variables for a step by step implementation.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...