Forum Discussion
Advocate IIHow to find valid values based on multiple tables
Hi guys,
I use this forum so much and I have almost found the answer I am after... Except for this one.
I have a table with:
- attributes name
- attributes values
- name of the valid-values reference table
- name of the specific valid-values column (if exists)
The first table below is basically my raw data:
| Attributes | Value | Data validation | Specific? |
| Num1 | 1 | TableNum | TN_C1 |
| Num2 | 2 | TableNum | TN_C1 |
| Num3 | 3 | TableNum | TN_C1 |
| Text1 | 4 | TableTxt | TT_C1 |
| Text2 | 5 | TableTxt | TT_C1 |
| Text3 | a | TableTxt | TT_C1 |
| Num4 | b | TableNum | |
| Num5 | 8 | TableNum | |
| Text4 | 9 | TableTxt |
The third column specifies another table name where to find the only possible valid values, being:
TableNum:
| TN_C1 | TN_C2 |
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| 5 | five |
| 6 | six |
| 7 | seven |
| 8 | eight |
| 9 | nine |
| 10 | ten |
and
TableTxt:
| TT_C1 |
| a |
| b |
| c |
| d |
| e |
| f |
If we want to make it more complex, the fourth column in the raw data table specifies which column to look for in the valid values table.
I am trying to get a calculated column (I believe?) that reports TRUE if the values are present in the specific reference table/column, otherwise FALSE.
I managed to get close to what I want by creating a calculate column for:
| Attributes | Value | Data validation | Specific? | Valid |
| Num1 | 1 | TableNum | TN_C1 | TRUE |
| Num2 | 2 | TableNum | TN_C1 | TRUE |
| Num3 | 3 | TableNum | TN_C1 | TRUE |
| Text1 | 4 | TableTxt | TT_C1 | FALSE |
| Text2 | 5 | TableTxt | TT_C1 | FALSE |
| Text3 | a | TableTxt | TT_C1 | TRUE |
| Num4 | b | TableNum | FALSE | |
| Num5 | 8 | TableNum | TRUE | |
| Text4 | 9 | TableTxt | FALSE |
8 Replies
ryan_mayuSuper User
is this what you want?
Column = if(LEFT('Table'[attributes],3)="Num" && ISNUMBER('Table'[Value]),TRUE(),FALSE())- ecalzavara
Thank you for your quick answer ryan_mayu . Unfortunately is not that easy. I am not only trying to find numbers but to associate the raw data with possibly many different tables containing a set of values, which make my data VALID or INVALID depending if the value on a specific row [Value] is found in the specified table [Data validation] (and, as next step, to be found in a specific column in that [Data validation] table, explicit in [Specific?] column.
Thank you for your go, really appreciated!
- ryan_mayu
sry, not clear about your request. so your raw data only contains two columns? How you get the third column and forth column?
