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.
Hi all,
In my data model, I have a fact table (named facRawDataPD_cat ) with the format below:
dateID custID variable value
01/07/2020 | 1 | variable1 | variable1_category_1 |
01/07/2020 | 2 | variable1 | variable1_category_2 |
01/07/2020 | 3 | variable1 | variable1_category_3 |
01/07/2020 | 4 | variable1 | variable1_category_4 |
01/07/2020 | 3 | variable2 | variable2_category_1 |
01/07/2020 | 1 | variable2 | variable2_category_2 |
01/07/2020 | 2 | variable2 | variable2_category_3 |
01/07/2020 | 4 | variable2 | variable2_category_3 |
01/07/2020 | 2 | variable3 | variable3_category_1 |
01/07/2020 | 3 | variable3 | variable3_category_2 |
01/07/2020 | 1 | variable3 | variable3_category_3 |
01/07/2020 | 4 | variable3 | variable3_category_4 |
02/07/2020 | 6 | variable1 | variable1_category_1 |
02/07/2020 | 5 | variable1 | variable1_category_2 |
02/07/2020 | 7 | variable1 | variable1_category_3 |
02/07/2020 | 8 | variable1 | variable1_category_4 |
02/07/2020 | 6 | variable2 | variable2_category_1 |
02/07/2020 | 5 | variable2 | variable2_category_2 |
02/07/2020 | 7 | variable2 | variable2_category_3 |
02/07/2020 | 8 | variable2 | variable2_category_3 |
02/07/2020 | 7 | variable3 | variable3_category_1 |
02/07/2020 | 5 | variable3 | variable3_category_2 |
02/07/2020 | 8 | variable3 | variable3_category_3 |
02/07/2020 | 6 | variable3 | variable3_category_4 |
03/07/2020 | 9 | variable1 | variable1_category_1 |
03/07/2020 | 10 | variable1 | variable1_category_2 |
03/07/2020 | 11 | variable1 | variable1_category_3 |
03/07/2020 | 12 | variable1 | variable1_category_4 |
03/07/2020 | 10 | variable2 | variable2_category_1 |
03/07/2020 | 11 | variable2 | variable2_category_2 |
03/07/2020 | 9 | variable2 | variable2_category_3 |
03/07/2020 | 12 | variable2 | variable2_category_1 |
03/07/2020 | 11 | variable3 | variable3_category_1 |
03/07/2020 | 10 | variable3 | variable3_category_2 |
03/07/2020 | 12 | variable3 | variable3_category_3 |
03/07/2020 | 9 | variable3 | variable3_category_4 |
and, of course i have also a calendar/dates table (named dimDates) that connects to this fact table by the dateID.
I have built the following metric to return the rolling % of customer by variable category across time (dates in this case) and according a certain amount of days, which the user can select to calculate - for example, a user might want to know how was the rolling customer% in the last 7 days for each variable category, in other cases the user might want to know how was the same figure for the last 15 days and so on. I also have a seperate table for this (names dimPeriods). Also, the user is alowed to select the variable (in a slicer) that he wants to analyse:
% Customers Selected Feature =
VAR SelDays =
SELECTEDVALUE ( dimPeriods[Days] )
VAR MaxDate =
LASTDATE ( facRawDataPD_cat[dateID] )
VAR SelFeature = [SelectedFeature]
RETURN
DIVIDE (
CALCULATE (
COUNT ( facRawDataPD_cat[value] ),
facRawDataPD_cat[variable] = SelFeature,
DATESINPERIOD ( dimDates[Date], LASTDATE ( dimDates[Date] ), - SelDays, DAY ),
dimDates[Date] <= MaxDate
),
CALCULATE (
COUNT ( facRawDataPD_cat[value] ),
facRawDataPD_cat[variable] = SelFeature,
ALL ( facRawDataPD_cat[value] ),
DATESINPERIOD ( dimDates[Date], LASTDATE ( dimDates[Date] ), - SelDays, DAY ),
dimDates[Date] <= MaxDate
)
)
As you can see, the metric has the following parameters:
- a period - which consists in the number of days the user want the calculations done
- a variable selection - which consists in the names of the variables in the fact table
- a Maximum date - which consists in the maximum date for whitch there is data available for calculation
This metric, although is returning correct results, its a bit slow and I would like to speed it up but don't know where to start to optimize it.
Before coming for your help, I have tried to "pull out" the
DATESINPERIOD ( dimDates[Date], LASTDATE ( dimDates[Date] ), - SelDays, DAY ),
dimDates[Date] <= MaxDate
but then the results weren't correct.
So, my question is, how can I optimize this metric given it is used to build a nice line chart with the dynamics (Period + variable selections) ?
Thanks in advance and sorry if the post is a little confusing 🙂
Solved! Go to Solution.
Hi all!
I just solved this problem myself 🙂
It turns out that it was
dimDates[Date] <= MaxDate
that was turning the query slow.
So, to solve this, what I did was to create a calculated column (yes, I know calculated columns are bad to use! 😛 ) in my dimDates that returns true whenever I have dates in my fact table, so I basicly passed the above exression to a calculated column. After that, i removed the above expression from my metric and I filtered my visual by the calculated column = True.
Please let me know if there is some better way to do this, but aparently my dax query is pretty quick now. 🙂
Thanks.
Hi all!
I just solved this problem myself 🙂
It turns out that it was
dimDates[Date] <= MaxDate
that was turning the query slow.
So, to solve this, what I did was to create a calculated column (yes, I know calculated columns are bad to use! 😛 ) in my dimDates that returns true whenever I have dates in my fact table, so I basicly passed the above exression to a calculated column. After that, i removed the above expression from my metric and I filtered my visual by the calculated column = True.
Please let me know if there is some better way to do this, but aparently my dax query is pretty quick now. 🙂
Thanks.
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |