Switch / Case multiple results
Hello all, I'm attempting to make an "error message" column in a table that can read multiple criteria and result the reasons there's an error message for the given project (rows in the table). I've found that Switch( True(),... ) only wants to find one criteria but I have overlapping cases where more than one case is true. Here's generally what I've got so far: Error Message = SWITCH( TRUE(), 'Data'[Start_Date]=BLANK(),"Start Date is blank", 'Data'[Status]="Red","Status is Red", //among several dozen other columns "No Errors") What's my alternative since if/elif doesn't seem to be availabile in dax? Any ways to cycle back through the cases? Ultimately I'd like to find a way to concatenate the various error messages with commas into a single column (per row) in a table. Any ideas from the community? Thanks.
Currency Format
Dear Community, I am working on a report where the users should be able to switch between 3 different currencies. I have created a simple table (CurrencyMeasure) and the following DAX command to support a currency slicer on the report page: NET SALES = IF( HASONEVALUE(CurrencyMeasure[Currency]), SWITCH(VALUES(CurrencyMeasure[Currency]) ,"DKK",FORMAT([Net Sales (DKK)], "#,## kr.") ,"£",FORMAT(DIVIDE([Net Sales (DKK)]8.5),"£ #,##") ,"€",(FORMAT(DIVIDE([Net Sales (DKK)],"€ #,##") ,[Net Sales (DKK)])) It all works fine, however the sorting of Net Sales gets rather odd in a table as its now a string (see picture). As I would prefer to have the currency symbols in the table, is there a way to sort by highest value and not first numer in a table? Thanks in advance.SWITCH and ISFILTERED combination
Hi all I am using a measure as follows: Fixed cost var filters = SWITCH( TRUE(), ISFILTERED(dDate[Year]), DIVIDE( CALCULATE( SUM('Fixed Cost per Month'[fixed Cost]), ALL('Fixed Cost per Month'[fixed Cost]) ) *MAX(dDate[Month Number]), [Dedicated Shipments] ), ISFILTERED(dDate[Month]), DIVIDE( SUM('Fixed Cost per Month'[fixed Cost]), [Dedicated Shipments] ) ) This when fed to visualization gives the below: for only [Year] filtered For [Months] filtered What I actually want from the measure is to give the "Actual Required" column values which is [Sum fixed cost / Dedicated Shipment] Month Dedicated Shipments Sum fixed cost Fixed cost var filters Actual required Jan 1800 910000 505.56 505.56 Feb 3288 910000 553.53 276.76 Mar 3553 910000 768.36 256.12 Apr 3726 910000 976.92 244.23 May 4397 910000 1034.8 206.96 Jun 3965 910000 1377.05 229.51 Jul 3381 910000 1884.06 269.15 Aug 5502 910000 1323.16 165.39 Sep 6396 910000 1280.49 142.28 Oct 6602 910000 1378.37 137.84 Nov 7656 910000 1307.47 118.86 Dec 5928 910000 1842.11 153.51 Here, Dedicated Shipments = CALCULATE([Shipments],'Prototype Modeling_Freight'[Transporter Type]="DEDICATED") Please let me know if any additional info is required here for you to arrive to a conclusion. All your comments and suggestions are greatly appreciated
Calculating UK National Insurance rates
Dear all, I am working on a costing overview but are challenged by adding UK National Insurance (NI) on top of the base salary for our employees. Employees are categories into different National Insurance Category Letters following the below rate structures on a weekly basis. My data is structured in daily shifts per employee with base salary [ActualCost] and NI Category in seperate coloums as seen below. Initial idea is to do a SWIFT/IF statement related to a seperate NI Cateogry table, however I cant get my head around it as the daily costs needs to be summed into Weekly costs in order to calculate the correct NI payable. Thank you so much in advance.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 40New 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" )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?
