topic Re: distinct Count of tour request numbers in DAX Commands and Tips

distinct Count of tour request numbers

Anonymous — Thu, 15 Feb 2024 11:05:50 GMT

hello team,
I have Append-QMR table in power bi. i have TOUR_REQUEST_NO and WEEK_NO and INQUIRY_DATE_KEY and TERMINATION_YEAR columns in that table. the INQUIRY_DATE_KEY is related with DATE_KEY column in M_INQ_DATE_D date table.In this table I have full date. I want to count the distinctcount of TOUR_REQUEST_NO created in in this week by taking refence INQUIRY_DATE_KEY .

Im getting more trouble with the dax function
i have used

DistinctCount_ThisWeek =

CALCULATE(

DISTINCTCOUNT('Append-QMR'[TOUR_REQUEST_NO]),

FILTER(

'Append-QMR',

'Append-QMR'[INQUIRY_DATE_KEY] >= FIRSTDATE(M_INQ_DATE_D[FULL_DATE].[Date]) &&

'Append-QMR'[INQUIRY_DATE_KEY] <= LASTDATE(M_INQ_DATE_D[FULL_DATE].[Date])

)

)fucction

Re: distinct Count of tour request numbers

nareshr89 — Thu, 15 Feb 2024 12:11:58 GMT

Try this DistinctCount_ThisWeek =
CALCULATE(
DISTINCTCOUNT('Append-QMR'[TOUR_REQUEST_NO]),
FILTER(

WEEKNUM(M_INQ_DATE_D[FULL_DATE].[Date])=WEEKNUM(NOW())

)

Re: distinct Count of tour request numbers

Anonymous — Thu, 15 Feb 2024 12:17:07 GMT

I tried this..one but in Date Table ..We do not have week column right?

Re: distinct Count of tour request numbers

nareshr89 — Thu, 15 Feb 2024 12:35:25 GMT

You dont need weekcolumn in specific as this information is picked up based on the WEEKNUM DAX function. Just the date column is required.

Re: distinct Count of tour request numbers

Anonymous — Thu, 15 Feb 2024 15:52:14 GMT

the error is

Re: distinct Count of tour request numbers

nareshr89 — Fri, 16 Feb 2024 05:55:15 GMT

Try this DistinctCount_ThisWeek =
CALCULATE(
DISTINCTCOUNT('Append-QMR'[TOUR_REQUEST_NO]),WEEKNUM('Append-QMR'[INQUIRY_DATE_KEY].[Date])=WEEKNUM(NOW())

)

If it still doesn't work share a sample PBIX file.

Re: distinct Count of tour request numbers

Anonymous — Fri, 16 Feb 2024 06:27:24 GMT

Re: distinct Count of tour request numbers

Anonymous — Fri, 16 Feb 2024 06:31:53 GMT

This is my model