New Column With Switch Statement Errors

[BrianNeedsHelp]

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

[SachinNandanwar]

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"

[Anonymous]

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.

[shafiz_p]

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

[BrianNeedsHelp]

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.  

[shafiz_p]

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

[VahidDM]

Hi   

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 

[BrianNeedsHelp]

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.  

[VahidDM]

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

[BrianNeedsHelp]

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.