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.
Dear community,
I am wondering what is wrong with my DAX formula or with my graph.
I made a graph with on the axis, date and on values I placed average per year.
The formula I am using to determine my average per year already got a date in it, it uses the date table.
I would like to get some help with figuring out where the problem is 🙂
average amount of unique visitors 2016 = CALCULATE(
AVERAGEX('amount of unique visitors','amount of unique visitors'[amount of unique visitors]),
FILTER(
ALL(Datedimension[Datekey]),
and (
'Datedimension'[Datekey]>= DATE(2016, 1 , 1),
'Datedimension'[Datekey]<= DATE(2016,12,31)
)
)
)
Thanks in advance 🙂
Solved! Go to Solution.
Hi!
Based on your description and the DAX formula provided, I see a couple of potential issues:
Naming Convention: The way you've named your tables and columns is a bit confusing. For instance, having a table named 'amount of unique visitors'
and then a column within that table named '[amount of unique visitors]'
can be misleading and cause potential mistakes.
AVERAGEX: This function averages the results of an expression calculated for each row in a table. It's important to ensure that the expression used inside AVERAGEX
is accurately capturing what you want to average. In your case, you're just referencing the column, which might not be yielding what you intend.
ALL function: When using the ALL
function in conjunction with FILTER
, you're effectively removing any filters on the Datedimension[Datekey]
and then reapplying them based on the conditions you specified in the FILTER
function. It's not inherently wrong, but it's worth noting in case there are any other filters being applied elsewhere in your model that you would want to consider.
Let's try to address the problems and refine the formula:
AverageUniqueVisitors2016 =
CALCULATE(
AVERAGE('amount of unique visitors'[amount of unique visitors]),
FILTER(
ALL(Datedimension[Datekey]),
Datedimension[Datekey] >= DATE(2016, 1 , 1) &&
Datedimension[Datekey] <= DATE(2016,12,31)
)
)
Hi!
Based on your description and the DAX formula provided, I see a couple of potential issues:
Naming Convention: The way you've named your tables and columns is a bit confusing. For instance, having a table named 'amount of unique visitors'
and then a column within that table named '[amount of unique visitors]'
can be misleading and cause potential mistakes.
AVERAGEX: This function averages the results of an expression calculated for each row in a table. It's important to ensure that the expression used inside AVERAGEX
is accurately capturing what you want to average. In your case, you're just referencing the column, which might not be yielding what you intend.
ALL function: When using the ALL
function in conjunction with FILTER
, you're effectively removing any filters on the Datedimension[Datekey]
and then reapplying them based on the conditions you specified in the FILTER
function. It's not inherently wrong, but it's worth noting in case there are any other filters being applied elsewhere in your model that you would want to consider.
Let's try to address the problems and refine the formula:
AverageUniqueVisitors2016 =
CALCULATE(
AVERAGE('amount of unique visitors'[amount of unique visitors]),
FILTER(
ALL(Datedimension[Datekey]),
Datedimension[Datekey] >= DATE(2016, 1 , 1) &&
Datedimension[Datekey] <= DATE(2016,12,31)
)
)
Hi @0xygen27
Do you have two tables? One contains vistors and a date table? The date table is marked as a date table? The date table has a one to many relationship with the Vistor table on the date column on both tables?
You can try these.
Total Unique Vistors = DISTINCTCOUNT(Vistors[UserID])
or using the above measure
Total Unique Vistors YTD = TOTALYTD([Total Unique Vistors], DateTable[Date])
Create a matrix with the Year from the date table in wither column or row section and then add the either measure to the Value section to get desired result
Thanks
Joe
If this post helps, then please Accept it as the solution
If you need to unique vistors,
Can you please tell me if the problem is still relevant? If you managed to solve it in some way, please write here how you solved this problem. Then it will be possible to mark this problem as solved.
If not, it may make sense to ask the question again, thus confirming the relevance of this problem.
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 |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |