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.
New Column With Switch Statement Errors
I have this Switch Statement that I put in a measure and it works fine in the measure for showing the results of "Low", Med", High". But I wanted to use the same formula in a column so that I could use it in a slicer and I'm getting this error when I select it to add to the visual. "The column week number specified in the 'SUMMARIZE' function was not found in the input table. ??? I don't have any Summarize function. Makes no sense. Any ideas? GACategory = SWITCH( TRUE(), [Gross Adds]<=45,"Low", [Gross Adds]>=45 || [Gross Adds]<=70,"Mid", [Gross Adds]>70, "High" )Dynamic Percentage of Parent Switch Order
Hi all, I am trying to do a percentage of parent calculation which is fine but I am using field parameters so users can dynamically change the row selections in a matrix. If the rows are selected in a specific order then the calculation works fine but if they are selected out of order with the switch calculation then the wrong result is returned. I created a dummy table to represent my issue where there are 3 categories, brand, group and country. DAX formula is: "%Share = VAR _brand = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Brand])) VAR _country = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Country])) VAR _group = CALCULATE([SUMVALUE],ALLSELECTED('Table'[Group])) RETURN SWITCH(TRUE(), ISINSCOPE('Table'[Brand]),DIVIDE([SUMVALUE],_brand), ISINSCOPE('Table'[Group]),DIVIDE([SUMVALUE],_group), ISINSCOPE('Table'[Country]),DIVIDE([SUMVALUE],_country), DIVIDE([SUMVALUE],CALCULATE([SUMVALUE],ALLSELECTED('Table'))) )" If I select country as first level in matrix then group as second level, it works correctly for both levels of the matrix. If I select group then country the second level is wrong. Presumably the group VAR is passing as true first giving sum of all groups in the selected country. So my question is, how can I get it to select the correct VAR regardless of the order the field parameters are selected? Dummy table I created here: Brand Group Country Value Brand1 Group1 GB 37 Brand2 Group1 GB 16 Brand3 Group2 GB 16 Brand4 Group2 GB 21 Brand5 Group2 GB 21 Brand6 Group3 GB 47 Brand7 Group3 GB 41 Brand8 Group3 GB 24 Brand9 Group3 GB 25 Brand1 Group1 FR 21 Brand2 Group1 FR 49 Brand3 Group2 FR 41 Brand4 Group2 FR 22 Brand5 Group2 FR 19 Brand6 Group3 FR 37 Brand7 Group3 FR 27 Brand8 Group3 FR 16 Brand9 Group3 FR 37 Brand1 Group1 DE 9 Brand2 Group1 DE 31 Brand3 Group2 DE 17 Brand4 Group2 DE 21 Brand5 Group2 DE 50 Brand6 Group3 DE 49 Brand7 Group3 DE 33 Brand8 Group3 DE 34 Brand9 Group3 DE 40
SWITCH AND SEVERAL VALUES to display in a card
I would like to know How is it possible to diplsay a value with a card when this one is selected in a filter? I tried with the formula "Selectedvalue" but first, if nothing is selected then the value displays "(Vide)" whereas i would like to display something else in text. Second point, if we select 2 values, is is possible to display those ones? Here is my exemple : SWITCH(SELECTEDVALUE(Temps[Trimestre affichage]),"Trim 1","Trimestre 1","Trim 2","Trimestre 2","Trim 3","Trimestre 3","Trim 4","Trimestre 4","") Several selection : How can we do if we have several value ? Because here I have just 4 values but if i have more? In add, if I select 2 values, it doesn't work (it displays nothing) I don't know how is it possible to mix all that. Thank you in advance for your return. Léna
Switch() function not working for all categories
I am using a switch function to display data in a certain order and tag certain values to measures. however, the result does not display the first and last category. Here is the DAX Report Value = (IF (AND (MAX ( Main[Is sum])=2,[Filtered]), BLANK (), SWITCH(MIN(Main[Is sum]), 1, [OpeningBalance], 2, (CALCULATE(SUM(Data[Debit/Credit Amount])), 3,(CALCULATE(SUM(Data[Debit/Credit Amount])), 4,[ClosingBalance] ))) ________________________________________________________________________________ Filtered = ISFILTERED(Submapping[Sub_grouping]) result is Opening balance and Closing balance are not shown. Each category is defined in the Main table and mapped to same numbers in Is Sum column. The total appearing as row subtotal is on. any guidance is appreciated.Help with formula including SWITCH, TRUE, ISBLANK
Hey guys, I have this column called COOKIE_ID and a column VISIT_ID. I want to figure out if a visitor (identified by COOKIE_ID) comes for the first time or is a returning visitor or an annonymous visitor. For this I figured I write the following formula: Cookie Status = VAR SelectedDateRange = DATESBETWEEN ( 'OD - All Page Visits Portal'[PAGE_LOAD_DATE], MIN ( 'OD - All Page Visits Portal'[PAGE_LOAD_DATE] ), MAX ( 'OD - All Page Visits Portal'[PAGE_LOAD_DATE] ) ) VAR CookieVisits = CALCULATETABLE ( VALUES ( 'OD - All Page Visits Portal'[COOKIE_ID] ), ALLEXCEPT ( 'OD - All Page Visits Portal', 'OD - All Page Visits Portal'[COOKIE_ID] ), SelectedDateRange ) VAR VisitCount = CALCULATE ( DISTINCTCOUNT ( 'OD - All Page Visits Portal'[VISIT_ID] ), ALLEXCEPT ( 'OD - All Page Visits Portal', 'OD - All Page Visits Portal'[COOKIE_ID] ), SelectedDateRange ) RETURN SWITCH ( TRUE (), ISBLANK ( 'OD - All Page Visits Portal'[COOKIE_ID] ), "Anoniem", VisitCount > 2, "Terugkerend", VisitCount = 1, "Nieuw", "Onbekend" ) For some odd reason though I can't select the column COOKIE_ID in ISBLANK. See picture below What am I doing wrong here?
