topic Re: Dax Counting Several date occurrence as one to get correct daily average in DAX Commands and Tips

Dax Counting Several date occurrence as one to get correct daily average

Anonymous — Thu, 19 May 2022 22:31:22 GMT

Hello my friend thank You in advance for Your help.

I am looking to get a daily request average. The issue is that some date are occuring more than once hence I am not able to get a perfect count. let's say per example for on 2/24 I received 10k request but each occurence a different time. Now I'm trying to count this occuence as one for 2/24 having 10k request and only one date which is 2/24.

this the data sample

This is the DAX I wrote

Daily Average = COUNT('Fact-Daily Access Request'[Access Request ID])/DISTINCTCOUNT('Fact-Daily Access Request'[Request Date]).

But I am not able to count date several occurence as one. I'm ok with the request ID to be counted several time but the date needs to only be counted once.

Thank You my friend

Re: Dax Counting Several date occurrence as one to get correct daily average

Whitewater100 — Fri, 20 May 2022 00:52:00 GMT

Hi:

Can you add a Date Table? Modeling> New Table then this code(Below). Afterwhich Mark Date field as Date Table and connect to your other fact table on Date fields.

Measure to Total Requests = SUM('Fact-Daily Access Request'[Access Request ID])

Daily Requests = AVERAGEX(Values(Dates[Date]), [Total Requests]))

It would be good to have dimension table for AccessRequest_ID that is unique and has relaionship with fact table. You can then sort by RequestorID.

I hope this helps! Thanks..

Daily Requests II(no weekends included) =
var weekdaytable = Filter(Dates, Dates[DayinWeek] <> 7 && Dates[DayinWeek] <> 1)
return
AVERAGEX(Weekdaytable[Total Requests])

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

Years,

"Month",MONTH([Date]),

"Year and Month Number",FORMAT([Date],"YYYY-MM"),

"Year and Month Name",FORMAT([Date],"YYYY-MMM"),

"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date

VAR Quarters = ADDCOLUMNS(

Months,

"Quarter",ROUNDUP(MONTH([Date])/3,0),

"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date

VAR Days = ADDCOLUMNS(

Quarters,

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Of Week",WEEKDAY([Date]),

"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date

VAR Weeks = ADDCOLUMNS(

Days,

"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

"Week of Year (Sunday)",WEEKNUM([Date],1),

"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

Weeks,

"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

Re: Dax Counting Several date occurrence as one to get correct daily average

Anonymous — Fri, 20 May 2022 02:57:35 GMT

Thanks You so much for Your input. I actually have Calendar table from another dashbaord, so I just copy and paste to this dashabord. I have the date column to make a connection to my fact table through Request Date column

However using this Dax Daily Requests = AVERAGEX(Values(Dates[Date]), [Total Requests])) or Total Requests = SUM('Fact-Daily Access Request'[Access Request ID]) doesn't give me accurate number.

Re: Dax Counting Several date occurrence as one to get correct daily average

Anonymous — Tue, 24 May 2022 06:40:02 GMT

Hi Anonymous ,

Do you mean that you want to count 10k times repeating Access Request ID on 2022/02/24 to only once?

My Sample:

Try this code to create measure or calcualted column.

Daily Average (Calculated Column) = CALCULATE ( DISTINCTCOUNT ( 'Fact-Daily Access Request'[Access Request ID] ), ALLEXCEPT ( 'Fact-Daily Access Request', 'Fact-Daily Access Request'[Request Date] ) )

Daily Average (Measure) = CALCULATE ( DISTINCTCOUNT ( 'Fact-Daily Access Request'[Access Request ID] ), ALLEXCEPT ( 'Fact-Daily Access Request', 'Fact-Daily Access Request'[Request Date] ) )

Result is as below.

Best Regards,
Rico Zhou

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

Re: Dax Counting Several date occurrence as one to get correct daily average

Anonymous — Tue, 24 May 2022 16:24:23 GMT

Thank You for Your input! In fact I am trying to first get the average daily count. In this example I'm trying to find what's the total of Access request ID for the Request date 2022-02-04, here the request date is being 'Duplicate' because they all come at different time during the day.
Then based on the total request I will need to find the over all daily average.

Thank You my friend.

Re: Dax Counting Several date occurrence as one to get correct daily average

Whitewater100 — Tue, 24 May 2022 17:03:47 GMT

Hello:

I understand, using last file suppplied your calc ol can be:

Daily Requests = CALCULATE(COUNTROWS('Fact-Daily Access Request'), ALLEXCEPT('Fact-Daily Access Request', 'Fact-Daily Access Request'[Access Request ID]))

Measure

Daily Requests(M) = CALCULATE(COUNTROWS('Fact-Daily Access Request'), ALLEXCEPT('Fact-Daily Access Request', 'Fact-Daily Access Request'[Access Request ID]))

Answer = 15. Does this solve? Thanks..