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.
SSAS consume a lot of memory
Hi, I am analyzing why my SSAS server is consume a lot of memory sometimes what i can see it is not when the model is processing. I have run the dmv Memory usage to find what in SSAS service is take all the memory, and we talk about 115GB memory. My model is only 6GB when loaded in memory. The result of running memory usage can you see below. The what i can see from it, it is the OBJECT ID Big and Small the consume all the memory togeheter. So my question is, what is that? And how can i reduce the consume of memory?
High CPU Usage on Power BI RS (On premise)
Hello guys, I have a question about Power BI Reporting Services (On Premise) One of the users posted a report that, when this report starts to update the data, consumes 100% cpu and 90% memory. Is there any way to know which report is this? So that I can guide you how to schedule the update And how to limit the use of cpu and memory in the service? Or even by report? regards,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?
