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 I need help with a measure that i am using in a coumn. i have 120 GB available and it runs out of memory. there is 20 million rows in the table
here is the measure
Solved! Go to Solution.
Hi, @Anonymous
Try formula as below:
HLC =
VAR T_DT =
MAX ( Full_FACT_SALES[Transaction_DateTime] )
VAR Active =
CALCULATE (
SUM ( Full_FACT_SALES[if active] ),
ALL ( Full_FACT_SALES[Transaction_DateTime] ),
Full_FACT_SALES[Transaction_DateTime] <= T_DT
)
VAR New =
CALCULATE (
SUM ( Full_FACT_SALES[if new] ),
ALL ( Full_FACT_SALES[Transaction_DateTime] ),
Full_FACT_SALES[Transaction_DateTime] <= T_DT
)
VAR Reactivated =
CALCULATE (
SUM ( Full_FACT_SALES[if Reactivated] ),
ALL ( Full_FACT_SALES[Transaction_DateTime] ),
Full_FACT_SALES[Transaction_DateTime] <= T_DT
)
VAR Risk =
CALCULATE (
SUM ( Full_FACT_SALES[if Risk] ),
ALL ( Full_FACT_SALES[Transaction_DateTime] ),
Full_FACT_SALES[Transaction_DateTime] <= T_DT
)
VAR Lapsed =
CALCULATE (
SUM ( Full_FACT_SALES[if Lapsed] ),
ALL ( Full_FACT_SALES[Transaction_DateTime] ),
Full_FACT_SALES[Transaction_DateTime] <= T_DT
)
RETURN
IF (
Active > 0,
"Active",
SWITCH (
TRUE (),
New > 0
&& Active = 0
&& Risk = 0
&& Lapsed = 0, "Risk",
Lapsed > 0
&& New = 0
&& Active = 0
&& Risk = 0, "Lapsed",
New > 0
&& Lapsed > 0
&& Active = 0
&& Risk > 0, "Reactivated",
New > 0
&& Lapsed > 0
&& Active = 0
&& Risk = 0, "Reactivated",
New > 0
&& Lapsed = 0
&& Active = 0
&& Risk > 0, "Reactivated",
""
)
)
Best Regards,
Community Support Team _ Eason
Hi, @Anonymous
Try formula as below:
HLC =
VAR T_DT =
MAX ( Full_FACT_SALES[Transaction_DateTime] )
VAR Active =
CALCULATE (
SUM ( Full_FACT_SALES[if active] ),
ALL ( Full_FACT_SALES[Transaction_DateTime] ),
Full_FACT_SALES[Transaction_DateTime] <= T_DT
)
VAR New =
CALCULATE (
SUM ( Full_FACT_SALES[if new] ),
ALL ( Full_FACT_SALES[Transaction_DateTime] ),
Full_FACT_SALES[Transaction_DateTime] <= T_DT
)
VAR Reactivated =
CALCULATE (
SUM ( Full_FACT_SALES[if Reactivated] ),
ALL ( Full_FACT_SALES[Transaction_DateTime] ),
Full_FACT_SALES[Transaction_DateTime] <= T_DT
)
VAR Risk =
CALCULATE (
SUM ( Full_FACT_SALES[if Risk] ),
ALL ( Full_FACT_SALES[Transaction_DateTime] ),
Full_FACT_SALES[Transaction_DateTime] <= T_DT
)
VAR Lapsed =
CALCULATE (
SUM ( Full_FACT_SALES[if Lapsed] ),
ALL ( Full_FACT_SALES[Transaction_DateTime] ),
Full_FACT_SALES[Transaction_DateTime] <= T_DT
)
RETURN
IF (
Active > 0,
"Active",
SWITCH (
TRUE (),
New > 0
&& Active = 0
&& Risk = 0
&& Lapsed = 0, "Risk",
Lapsed > 0
&& New = 0
&& Active = 0
&& Risk = 0, "Lapsed",
New > 0
&& Lapsed > 0
&& Active = 0
&& Risk > 0, "Reactivated",
New > 0
&& Lapsed > 0
&& Active = 0
&& Risk = 0, "Reactivated",
New > 0
&& Lapsed = 0
&& Active = 0
&& Risk > 0, "Reactivated",
""
)
)
Best Regards,
Community Support Team _ Eason
Switch is a good suggestion i will try that now.
Yeah, you should check out my Performance Tuning DAX series (4 parts) here: https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
Performance Tuning DAX is no joke, it is involved and not sure I can really help without having access to the data and being able to experiment. Your nested IF statements should be replaced with a SWITCH statement. Logic can likely be improved. You have a lot of repeated code that you should use variables for. You use the same FILTER over and over again, store that FILTER in a table variable and do a SUMX across it for each of your New, etc. variables.
Just a few thoughts.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |