DAX for evaluating flags in large table
I have a dashboard with the following limitations: Over 40 million rows in the main fact table. The dashboard compares two time periods that are selected by the user. The time periods are internally defined (not dates) and are selected by the user using two slicers. There is some "same store" logic which requires the use of an IF statement in the majority of measures. Other measures are built on top of these base measures. This approach uses a pre-calculated pattern of time periods assigned to each store and joined to the main fact table. Example measure: measure_1 = VAR PeriodSelection = SELECTEDVALUE( same_store_filter[Value] ) VAR Result = IF( PeriodSelection = "All Stores", CALCULATE ( SUM( fact_performancesummary[unitcapacitycnt] ), fact_performancesummary[resettimingdesc] IN VALUES ( compare_case_1[resettimingdesc] ) ), CALCULATE ( SUM( fact_performancesummary[unitcapacitycnt] ), fact_performancesummary[resettimingdesc] in VALUES(compare_case_1[resettimingdesc]), dim_same_store_status[resettimingdesc_2] in VALUES(compare_case_2[resettimingdesc]) ) ) RETURN Result Problem: One of the tabs on the dashboard is essentially a large, very granular data dump with a calculation that compare values for the two time periods and evaluates them as "Added" (new products), "Deleted" (removed products), or "Maintained" (products in both time periods). If I use an IF or SWITCH statement for this secondary evaluation, the visual chokes on anything over a few thousand rows. However, if I use a DIVIDE statement with the two values, I can create a conditionally formatted icon that displays close to 500K rows. (The conditional formatting is based on the three states resulting from the DIVIDE: 1) a positive number; 2) a negative #1 (the error default), and 3) blank or zero. The user wants to be able to export an actual text flag but the conditional formatting just downloads as the underlying numeric results. Example: _flag = DIVIDE([measure_1],[measure_2],-1) Conditional Formatting: Visual: I'm looking for ways to efficiently display a text-based flag that the user can export.
DAX Out of memory error in a measure
Greetings, I am getting a Out of memory error in a measure. I have over 16 GB of ram. The measure is below Average Manage Score = aVERAGEX( aLL(Education_Metrics), [Tiered Manage Education Score]) Tiered Manage Education Score = ([Tiered Manage Courses Weight] +[Manage Certification Weightage] +[Manage Life Cycle Weightage 2] )/[Manage Seats Weightage 3] Any tips what could cause this issue to go away?
