Forum Discussion
vpsoini
Helper I
Helper IFreetext data mapping into pre-defined values or "others"
Hello. I'm trying to list models of sold units into a table, but the issue is that model name/number is a freetext field, so sometimes values are not excatly correct. I have ledger table, where ...
Hi vpsoini ,
Here are the steps you can follow：
1. Create calculated column.
Flag = var _1= DISTINCT( SELECTCOLUMNS( 'Table2',"Uniformed Model","undefined")) var _2= DISTINCT('Table2'[Uniformed Model]) return UNION( _2,_1)2. Create calculated table.
Sales = var _groupcolumn = SELECTCOLUMNS( FILTER('Table2','Table2'[Uniformed Model]=EARLIER('Flag'[Uniformed Model])), "1", [Original model]) var _allcolumn= SELECTCOLUMNS( 'Table2', "1", [Original model]) var _sum= SUMX( FILTER(ALL(Table1), 'Table1'[Model] in _groupcolumn),[Amount]) return IF( _sum = BLANK(), SUMX( FILTER(ALL(Table1), NOT('Table1'[Model]) in _allcolumn),[Amount]),_sum)3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi vpsoini ,
Here are the steps you can follow：
1. Create calculated column.
Flag =
var _1=
DISTINCT(
SELECTCOLUMNS(
'Table2',"Uniformed Model","undefined"))
var _2=
DISTINCT('Table2'[Uniformed Model])
return
UNION(
_2,_1)
2. Create calculated table.
Sales =
var _groupcolumn =
SELECTCOLUMNS(
FILTER('Table2','Table2'[Uniformed Model]=EARLIER('Flag'[Uniformed Model])),
"1",
[Original model])
var _allcolumn=
SELECTCOLUMNS(
'Table2',
"1",
[Original model])
var _sum=
SUMX(
FILTER(ALL(Table1),
'Table1'[Model] in _groupcolumn),[Amount])
return
IF(
_sum = BLANK(),
SUMX(
FILTER(ALL(Table1),
NOT('Table1'[Model]) in _allcolumn),[Amount]),_sum)
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly