Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 item sales are listed with columns:
Date/Model/Quantity/Price (below samples with Tesla examples) , where Model column is not a drop-down with pre-defined values, but freetext field
Date | Model | Quantity | Amount |
21.02.2023 | Model 3 | 1 | 50000 |
19.02.2023 | Model X | 2 | 200000 |
16.02.2023 | 3 | 1 | 50000 |
02.02.2023 | S | 1 | 100000 |
24.01.2023 | X-model | 1 | 100000 |
15.01.2023 | big sedan | 1 | 125000 |
13.1.2023 | Mdel 3 | 1 | 55000 |
And another "helper table" where I have tried to uniform some of the manually entered "wrong" values into right models / model series. This table has e.g. columns
Original model/uniformed model/model series (once again with Tesla examples)
Original model | Uniformed Model | Series |
Model 3 | Model 3 | 3 |
3-model | Model 3 | 3 |
3 | Model 3 | 3 |
Model X | Model X | X |
X-model | Model X | X |
X | Model X | X |
Model S | Model S | S |
S-model | Model S | S |
S | Model S | S |
These 2 tables are connected with Model - Original Model link and I'm using "uniformed model" as my key in the table visual.
Since the model in the sales ledger table is freetext, users can have whatever kind of typos etc in that field and if I don't have that corresponding typo defined in the helper table, that value will not be calculated within that table. In my example ledger those 2 earliest values have model written incorreclty in a way, which is not defined in the helper table, so they would not be visual in my table
So the question is: what would be the easiest way to define value "undefined" into the table visual, where all those model "typos" from ledger could be collected in order to get total sales values. The example table visual could be e.g. total sum:
Uniformed model | Sales |
Model 3 | 100000 |
Model S | 100000 |
Model X | 300000 |
undefined | 180000 |
Total | 680000 |
So how can I define something, that can be basically anything (but those pre-defined values), into one group named "undefined" ?
Thanks a lot for your solution proposals 😊
Solved! Go to Solution.
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
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |