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.
First occurrence in a measure
Hi folks, I've been scratching my head over this issue and read through multiple forum posts but nothing has helped me so far. Sample data below: I have four fields here: Index - unsummarized column from my source (column) Wafer Sum = Summarized column Cumulative Sum = Running total of Wafer Sum per Index column (Quick Measure) Flag = Toggles between Yes and No based on following condition ( [Cumulative Sum] >= [Wafer Sum] * 0.5) (Measure) What I'm trying to accomplish is get the first occurrence when the Flag = "Yes" and return the Index value. In this case, the Index value returned should be 25. However, I cant use most of the functions such as EARLIER, MINX or CALCULATE since they are catered towards columns and not measures. Any help on this issue is appreciated. Thanks in advance!
