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 everyone. I'm hoping to get some help on improving my DAX measures. I have a Dimension table of about 55k rows and a bunch of Fact tables. What I'm trying to do is to create measures and KPI from fact tables and summarize them for each row in the Dimension table. It's pretty straight forward, but what I need is running total of those measures as of today and running total of thsoe measures as of n days ago. Running totals as of today all run pretty fast. The issues I'm having are with the running totals as of n days ago. n is a numeric paramter that report user can change through a slicer. It's getting me what I want, but running a little bit slow (about 2-3 mins to get it return for all rows in the Dim table). One example of such DAX measure is as below. Is there any way to improve the efficiency? Any insight is appreciated.
Count - Running Total As Of Days Ago =
CALCULATE(
COUNT(Fact1[ID]),
USERELATIONSHIP(Fact1[DateID], DimDate[DateID]),
FILTER(
ALL(DimDate),
DimDate[xDaysAgo]>=DaysAgo_Parameter[DaysAgo_Parameter Value]
),
FILTER(
ALLSELECTED(Fact1[DateID]),
Fact1[DateID]<=MAX(DimDate[DateID])
)
)
@DataSundowner Try:
Count - Running Total As Of Days Ago =
CALCULATE(
COUNT(Fact1[ID]),
USERELATIONSHIP(Fact1[DateID], DimDate[DateID]),
DimDate[xDaysAgo]>=DaysAgo_Parameter[DaysAgo_Parameter Value],
Fact1[DateID]<=MAX(DimDate[DateID])
)
If you don't require the USERELATIONSHIP you could try this method: Better Running Total - Microsoft Power BI Community
Hi @Greg_Deckler Thank you for your response. I tried your method, but got the following error message.
"A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
@DataSundowner Any chance you can post sample data? Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
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 |
---|---|
107 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |