Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hello:
I understand, using last file suppplied your calc ol can be:
Answer = 15. Does this solve? Thanks..
Hi @Rio007 ,
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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |