Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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 |
is this what you want?
Column = if(LEFT('Table'[attributes],3)="Num" && ISNUMBER('Table'[Value]),TRUE(),FALSE())
Proud to be a Super User!
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!
sry, not clear about your request. so your raw data only contains two columns? How you get the third column and forth column?
Proud to be a Super User!
Nope,
My raw data (in the example provided) contains 4 columns (the formatting on the forum is not the best):
| Attributes | Value | Data validation | Specific? |
The Data validation column (and, if present, the Specific? one) defines the name of the table where the valid set of values are to be found for that specific attribute.
I hope this clarifies it.
Thanks
what do you want now? check if value exists in the data validation table?
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 |
value 1 from TableNum, then true? value 9 not from TableTxt then FALSE?
Proud to be a Super User!
"value 1 from TableNum, then true? value 9 not from TableTxt then FALSE?"
Correct
@Anonymous @ecalzavara
pls see the attachment below
Proud to be a Super User!
Thank you @ryan_mayu .
This relies on manually define the variables, right?
So, if I have 30 different Data Validation table I'd need to define the variables one by one?
What I am trying to get is that the definition of the validation tables are in the raw data (which I have) and that this specifies where to go and find the valid values. I am trying something like the IF(...IN DISTINCT()) approach, but that moves away from having to hard-code (variables or many different, possibly hundreads of tables) in the DAX. As I have already everything defined, either in the raw data or in the validation table, I would like to move away from the variables (unless they can be parametrised and created automatically from information in the raw data table).
Thank you
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |