Switchif generated lookup column?

Hi there,

I’m looking to use a lookup column generated by a switchif formula, then count that data in another table. But for some reason I’m getting some kind of error when I try.

I set up a doc for testing here: Untitled

Thank you!

You’ll need to specify the actual row from the other table, rather than just the text I think. So instead of

"Low Confidence"

you’ll do

[Confidence group].Filter([Confidence group] = "Low Confidence").First()

The first [Confidence group] is your table, the second [Confidence group] is your column. The First() is probably unnecessary, it’s just in case you create two rows in Confidence Group table with the same label, this’ll grab the first instance and leave it at that.


Thanks for this response. Doing this seems to make the formula a circular reference?

Hi @Tim_Richardson1 :blush: ,

I’m just here to say that @Nick_HE was right :wink: . I’ve implemented his suggestion in your sample doc (see both columns named Copy of...) :blush: .

In your actual SwitchIf(), you just write “Low Confidence” (or other) which is just a simple text value the lookup inserts, not an actual row from your Confidence Group] table.
To retrieve the appropriate row from your [Confidence Group] table, you need to filter that table by [Confidence group]="Low Confidence" (for example) :blush:
([Confidence group] being in fact CurrentValue.[Confidence group]).

And the whole SwitchIf() is looking like this :

  thisRow.[Avg KR Conf] < 3, 
  [Confidence group].Filter([Confidence group]="Low Confidence"), 
  thisRow.[Avg KR Conf] = 3,
  [Confidence group].Filter([Confidence group]="Medium Confidence"), 
  thisRow.[Avg KR Conf] > 3, 
  [Confidence group].Filter([Confidence group]="High Confidence"),
  thisRow.[Avg KR Conf].IsBlank(),
  [Confidence group].Filter([Confidence group]="Confidence Score Missing")

Once that’s in place, you can Count() those occurrences in your [Confidence Group] table :blush: .

Hope this helps :blush:

1 Like

Thank you @Nick_HE and @Pch !! It wouldn’t work as first because I was referencing the wrong place. Thanks so much!@

1 Like

No problem @Tim_Richardson1 :blush: !

Glad to know you were able to solve this mystery :raised_hands: :grin: !

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.