Using Switch and Contains for Multiple Conditions
Hello, I have a dataset with two specific columns, Marketing_grouping and campaign_name. I want to create a new column that reads the text string in the campaign_name column and returns a value. We want to identify if the campaign is a Brand or NonBrand campaign. There are other campaigns that are not Brand or Nonbrand specific, so if possible, the new column would return the Marketing grouping. Essentially we are splitting the Marketing_Grouping, Paid Search, into 2 groups, brand and nonbrand, and using the campaign name column to identify. Also, there are paid search campaigns that do no have brand or nonbrand in Its name and we will group them under nonbrand ** Case sensitive: If campaign_name contains NB, Non, non, Non-Brand, and Marketing group = Paid Search, then NB If Campaign_Name contains Brand, BR, and marketing group = Paid Search, then Brand If Marketing_group = Paid Search and Campaign name doesn't contain any of the above, then NB Else, Marketing_Group (all other non paid search campaigns, use marketing group)
Combined SWITCH and SELECTEDVALUE function not working for sum of multiple measures
Hi all, I'm trying to set up a DAX-based table visual for a financial statement - based on financial transactions. My current set up is now based on a YouTube guide (https://www.youtube.com/watch?v=J4317R5BvsA&t=539s). The major difference between my approach and the one in the YouTube guide is that I'm working on financial transactions rather than aggrecated categories. So far so good, but I'm running into trouble when trying to get values from a measure that sums other measures e.g. a gross margin, which shows a blank. My measure for retrieving and lining up the correct line item values looks like this: The measures that do work, are all written like this: The measures that don't work, are all written like this: I'm not a super user of Power BI at all, but I suspect that the "sum measures" shows a blank due to the fact that my "sum measures" contain multiple measures. I tried replacing the DAX code with a simple hard typed numbers (e.g. 1000-500-250) formula, which does show up in my table. Any ideas for a workaround or a totally different approach is much appreciated. Happy holidays! 😎 Best regards, ChrisChecking Data Completeness
Hi I am new to PowerBI/PowerQuery, the following task might be trivial to some of you, but your help is highly appreciated 😁. For example: I have three columns, where each column is presented from different files Vendor Name Masterfile A Masterfile B Status A xxx xxx Complete B xxx Incomplete C Incomplete I want to develop a new column called "Status" where it will examine the completeness of column Masterfile A and Masterfile B. When BOTH columns were filled it will say "Complete" as in Vendor A When ONE column or BOTH are NOT filled it will say Ïncomplete" as in Vendor B or C I think the best approach to this is using the Switch True function of If function. I might be wrong. Regards
Switch, True & ISINSCOPE
Hi Everyone! I have the following measure, which depending on what level of the organisation is selected in the slicer will show on a clustered column and line visual either the Headcount Budget or blank/nothing. The reason being is that we only had budget set at domain level, and i wanted to ensure when someone selects below this level they would see no data for headcount budget. This has now changed and for cc_domain = "non-domain" we have headcount budgets set for the obs_level3, what I cant work out is how to change the code below to show headcount budget at obs_level3 where cc_domain = "non-domain", but not where cc_domain <> "non-domain" Headcount_Budget_Wkly_Snap = VAR HC = "" VAR HCB = CALCULATE([Headcount Budget]) VAR obs_l7 = ISINSCOPE(OBS_MAPPING[obs_level7]) VAR obs_l6 = ISINSCOPE(OBS_MAPPING[obs_level6]) VAR obs_l5 = ISINSCOPE(OBS_MAPPING[obs_level5]) VAR obs_l4 = ISINSCOPE(OBS_MAPPING[obs_level4]) VAR obs_l3 = ISINSCOPE(OBS_MAPPING[obs_level3]) VAR domain_sel = ISINSCOPE ( CC_OWNERS_LN[cc_domain] ) RETURN SWITCH ( TRUE (), obs_l7, HC, obs_l6, HC, obs_l5, HC, obs_l4, HCA, obs_l3, HCB, domain_sel, HCB, HC ) Any thoughts, is it even still possible to use a varient of the above code for this new scenario? Cheers AndySwitch Restricts to evaluate all the conditions
Using SWITCH the first condition met defines the result. In the following example, the second condition (<= 150) will never be met, because the first one is less restrictive. Is there any solution so that switch should go to each and every condition, Order of condition should not matter. DEFINE MEASURE Sales[Discounted Sales] = SUMX ( SUMMARIZE ( Sales, Sales[Net Price], Product[Category] ), VAR DiscountPct = SWITCH ( TRUE, Sales[Net Price] <= 1000, 0.2, Sales[Net Price] <= 150, 0.15, Product[Category] = "Audio", 0.13, 0 ) RETURN [Sales Amount] * (1 - DiscountPct ) ) EVALUATE SUMMARIZECOLUMNS ( 'Product'[Category], "Sales Amount", [Sales Amount], "Discounted sales", [Discounted Sales] ) ORDER BY [Category] #Power_BI #DAX_CommunityDAX formula for financial report
Hi all, i'm working on a financial report in PowerBI and i already had a lot of inspiration from the internet, but there is one problem now and I really don't know how to sort this. I have caclulated some subtotals, for "omzet", 'Brutowinst" and 'Operationele kosten'. this all works. Then for all other costs, i have made a calculation called 'Actuals'. This also works fine. Now i have an Financial template with RowID to make sure the table will be in the correct sorting order and to make sure the row names will look properly with some spaces before descriptions. So this is my model: I'm told that you should not link the financial template to the rest of the tables, as it is only used for rownames and the values will have to be calculated and not linked. Then, i have made a DAX formules that should decide in the financial template table (on the right) if there is a subtotal calculated or not. If yes, it should return the subtotal amount (this works fine), if no, it should give the 'actual' amount (as in the table on the left). But as you can see, it returns the total of the acuals almost everywhere, and not the individuals actuals for each row. But what i want, is a subtotal and otherwise the acual amount fot that specific row. These are the formulas I have used now: for the actuals: for the Selected Years Actuals: Probably I made a mistake in the CALCULATE part at the end? But I don't know how to fix it, so i hope somebody can help me with this? It would be much appreciated! Kind regards, MariekeConcatenate a field within IF/SWITCH statement
Hello Everyone, I'm new to Power BI. I have created a new column in my report using this SWITCH statement to group Institutions based on the below conditions. What I want is Concat "Control Group1" with INSTITUTIONS[NAME] within the SWITCH statement. Tried Using CONCATENATE and COMBINEVALUE functions as well as below method. All of those gives me " Error Message: OLE DB or ODBC error: [Expression. Error] We couldn't fold the expression to the data source. Please try a simpler expression.." Error. I'm using Direct Query mode in My Report. Appreciate If anyone can suggest me a method to concat a string with Table[ColumnName] within a SWITCH Statement INSTITUTIONS GROUP= SWITCH ( TRUE (), INSTITUTIONS[ID] = "d3a2efa8" || INSTITUTIONS[ID] = "721be8893","Control Group1" & " - " & INSTITUTIONS[NAME], INSTITUTIONS[ID]="729dbb5a-" || INSTITUTIONS[ID] ="b7ea8f29" || INSTITUTIONS[ID] ="6d85fa58" || INSTITUTIONS[ID] ="1df7d3ed","Control Group2","Control Group3") Thanks RushiSCompare master column to two other columns
I haven't quite found my exact issue in an existing forum post; if there's something already out there on this topic I'd be happy to take a look. I have a table that contains a list of Contact IDs - each numerical ID corresponds to a person. I have two other tables with active relationships to this table - one is a table that has a list of Contact IDs for competitors, and one that is a table that has a list of Contact IDs for alumni. In my All Contacts table, I have created two calculated columns (CompetitorSwitch and AlumniSwitch) to identify the status of each contact for these two categories: What I'm trying to do here is create a new column (or measure if that makes more sense) that tells me when a contact is BOTH a competitor and an alumni. I've tried IF statements, SWITCH/TRUE() statements, calculated tables...it's all failed me. I'm not sure if it's a mistake at this stage, or if I messed up by creating the two SWITCH columns first. You'll notice in the data model screenshot that the Alumni and Competitor tables do not have an active relationship, ruling out RELATED and similar functions. I'm completely stuck...open to ideas, and can add more info on the data model if needed. Thanks in advance!
