Forum Discussion
Helper IIIntersect formula to sum up values
This seems much simpler than the switch.
Account status growth = VAR CurrStatus = SELECTEDVALUE ( 'Account status'[Account status] ) RETURN CALCULATE ( [Growth], FILTER ( 'Account_Status_Table_Segment', [Account status segment] = CurrStatus ) )Variables will help with these measures too.
Account status platform = VAR Threshold = SELECTEDVALUE ( 'Selection: account status threshold'[Threshold] ) VAR CurrPeriod = [Current Period] VAR PrevPeriod = [Previous Period] VAR GrowthPct = [Growth %] VAR CurrPeriodIsBlank = ( ISBLANK ( CurrPeriod ) || ROUND ( CurrPeriod, 0 ) = 0 ) VAR PrevPeriodIsBlank = ( ISBLANK ( PrevPeriod ) || ROUND ( PrevPeriod, 0 ) = 0 ) RETURN SWITCH ( TRUE (), CurrPeriodIsBlank && PrevPeriodIsBlank, "Inactive", CurrPeriodIsBlank && ROUND ( PrevPeriod, 0 ) > 0, "Lost", GrowthPct <= - Threshold, "Lost", PrevPeriodIsBlank && ROUND ( CurrPeriod, 0 ) > 0, "Gained", GrowthPct >= Threshold, "Gained", GrowthPct >= 0.05, "Gaining", PrevPeriod <> 0 && GrowthPct <= -0.05, "Losing", "Stable" )That should help some but what would really help is if you could keep this from computing for every single row of the table you're filtering, so the critical question is how is this measure related to the row context of 'Account_Status_Table_Platform'? Is there anything that changes from row to row that changes the output of this table? How are [Current Period] and [Previous Period] defined? Do these depend on 'Account_Status_Table_Platform' at all?
Super UserI can't think of something much better. You could move stuff around to make it look different but I don't see a way to avoid the general structure.
You'll likely get better performance filtering on single columns rather than entire tables though. See if this is any faster:
Account status growth (with account and product lvl) =
VAR CurrStatus = SELECTEDVALUE ( 'Account status'[Account status] )
VAR CurrAccountlvl = SELECTEDVALUE ( 'Account level'[Account level] )
VAR CurrProductlvl = SELECTEDVALUE ( 'Selection: product level'[Product level] )
VAR PlatformCAC =
TREATAS ( { CurrStatus }, 'Account_Status_Table_Platform'[Account status Platform] )
VAR PlatformPAC =
TREATAS ( { CurrStatus }, 'Paccount_Status_Table_Platform'[Parent Account status platform] )
VAR ProductCAC =
TREATAS ( { CurrStatus }, 'Account_Status_Table_Product'[Account status product] )
VAR ProductPAC =
TREATAS ( { CurrStatus }, 'Paccount_Status_Table_Product'[Parent Account status product] )
VAR SegmentCAC =
TREATAS ( { CurrStatus }, 'Account_Status_Table_Segment'[Account status segment] )
VAR SegmentPAC =
TREATAS ( { CurrStatus }, 'Paccount_Status_Table_Segment'[Parent Account status segment] )
RETURN
SWITCH (
CurrProductlvl,
"Platform",
SWITCH (
CurrAccountlvl,
"CAC", CALCULATE ( [Growth], PlatformCAC ),
"PAC", CALCULATE ( [Growth], PlatformPAC )
),
"Product",
SWITCH (
CurrAccountlvl,
"CAC", CALCULATE ( [Growth], ProductCAC ),
"PAC", CALCULATE ( [Growth], ProductPAC )
),
"Segment",
SWITCH (
CurrAccountlvl,
"CAC", CALCULATE ( [Growth], SegmentCAC ),
"PAC", CALCULATE ( [Growth], SegmentPAC )
)
)
Helper IIThank you for the advice! Unfortunately the suggested formula doens't work because all account status forumlas such as "Account_Status_Table_Platform'[Account status Platform]" are measures. They are measures and not columns because they should change based on user selection of point in time and time comparison.