topic Re: New Column With Switch Statement Errors in DAX Commands and Tips

New Column With Switch Statement Errors

BrianNeedsHelp — Wed, 06 Nov 2024 02:12:51 GMT

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" )

Re: New Column With Switch Statement Errors

VahidDM — Wed, 06 Nov 2024 02:20:02 GMT

Can you share the details of [Gross Adds]?

BTW, can you use the column name rather than the measure refrence in the calculated column or add that measure explanation directly in the calculated column DAX query?

Appreciate your Kudos!!

LinkedIn | Twitter | Blog | YouTube

Re: New Column With Switch Statement Errors

BrianNeedsHelp — Wed, 06 Nov 2024 03:21:28 GMT

Hi. [Gross Adds] is like an aggretate of new subscribers to a newspaper. I'm not sure what you mean by adding that measure explanation directly. I'm new to this, so i just clicked on add column and named it GACategory. It put an fx next to the name and I just checkmark the box to add it to the visual. That's when the whole visual goes blank and i get that error.

Re: New Column With Switch Statement Errors

VahidDM — Wed, 06 Nov 2024 04:10:24 GMT

Would you please share that [Gross Adds] DAX query here?

Re: New Column With Switch Statement Errors

BrianNeedsHelp — Wed, 06 Nov 2024 04:15:46 GMT

Well it just shows up as a field in the table 'Subscriber Activity'. I don't see any query. I was just given access to this a couple weeks ago, so I'm not certain if i have any other access except building reports if that makes sense.

Re: New Column With Switch Statement Errors

shafiz_p — Wed, 06 Nov 2024 06:50:15 GMT

Hi BrianNeedsHelp
In DAX, measures and calculated columns serve different purposes and contexts. Unfortunately, you cannot directly use a measure in a calculated column because measures are evaluated in the context of the entire dataset, while calculated columns are evaluated row by row within a table.

You need to ensure that the logic used in your measure is translated into a row-by-row context.

You can try this:

GACategory = VAR GrossAddsValue = [Gross Adds] -- Replace this with the logic to calculate Gross Adds for each row RETURN SWITCH( TRUE(), GrossAddsValue <= 45, "Low", GrossAddsValue > 45 && GrossAddsValue <= 70, "Mid", GrossAddsValue > 70, "High" )

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

Re: New Column With Switch Statement Errors

Anonymous — Thu, 07 Nov 2024 09:21:18 GMT

Hi BrianNeedsHelp ,

In my experience, it should be a field in the calculation table, and perhaps this case requires aggregation or other adjustments.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Re: New Column With Switch Statement Errors

BrianNeedsHelp — Thu, 07 Nov 2024 14:03:55 GMT

I think there was another visual causing errors. So I started over on another tab, and put this in like you mentioned:

GACategory = VAR GAs = sumx(VALUES('Location'[Location]),[Gross Adds]) REturn SWITCH( TRUE(), GAs<=45,"Low", GAs>=45 && GAs<=70,"Mid", GAs>70, "High" )

Now I'm getting "this query uses more memory than the limit". So it may be that I need to query it down further? Not certain but the same thing works perfect in a measure.

Re: New Column With Switch Statement Errors

shafiz_p — Thu, 07 Nov 2024 18:38:10 GMT

Your used functions are quite memory-intensive especially with large datasets. Anyway, if possible please share pbix. Without knowing what you are trying to achieve it is hard to give solution.

Solution provided earlier was not any concreate solution. Just want you to remember, that calculated column works row by row. It checks that the current row satisfy condition, if, then put it in calculation.

I don't know about your [Gross Adds] measure or it is a column or not. You are summing up gross adds for each location and then compare value to tagging each row (In which table). So, what is the relation between each row (where this column will be developed) and the gross adds, not very clear.

For example, I have a product table with price column where each row is a complete product info. I want to tag price with Low, Mid and High. I would then do simple:

Category =
SWITCH (
TRUE(),
Product[Price] <= 100, "Low",
Product[Price] > 100 && Product[Price] <= 500, "Mid",
"High"
)

Re: New Column With Switch Statement Errors

SachinNandanwar — Fri, 08 Nov 2024 07:18:29 GMT

Define a new range table say RangeTable with the following columns and values

Display | Min | Max "Low" | 1 | 45 "Mid" | 46 | 70 "High" | 71 | 1000000000 (Or any other maximum value)

And then use the following in your calculated column :

CALCULATE ( VALUES ( 'RangeTable '[Display] ), FILTER ( 'RangeTable', 'YourTable'[Gross Adds] >= 'RangeTable'[Min] && 'YourTable'[Gross Adds] <= 'RangeTable'[Max] ) )

and also your formula has an error.

When the "Gross Adds" has a value of 45 it would satisfy condition for both Low and High

[Gross Adds]<=45,"Low", [Gross Adds]>=45 || [Gross Adds]<=70,"Mid", [Gross Adds]>70, "High"