Forum Discussion
Dynamic Attribute Mapping Measure
Venson
Not 100% clear. However, please try the followingMapping Ratio = VAR SelectedProduct = SELECTEDVALUE ( Products[Product] ) VAR SelectedTable = SELECTCOLUMNS ( FILTER ( ALL ( 'Table' ), 'Table'[Product] = SelectedProduct ), "A", 'Table'[AttributeA], "B", 'Table'[AttributeB], "C", 'Table'[AttributeC], "D", 'Table'[AttributeD] ) VAR CombinedTable = CROSSJOIN ( 'Table', SelectedTable ) VAR Matches = SUMX ( CombinedTable, VAR Condition1 = INT ( 'Table'[AttributeA] = [A] ) VAR Condition2 = INT ( 'Table'[AttributeB] >= [B] ) VAR Condition3 = INT ( 'Table'[AttributeC] >= [C] ) VAR Condition4 = INT ( 'Table'[AttributeD] = [D] ) RETURN Condition1 * ( Condition2 * ( Condition1 + Condition2 + Condition3 * ( Condition3 + Condition4 ) ) ) ) RETURN IF ( HASONEVALUE ( 'Table'[Product] ), DIVIDE ( Matches, 4 ) )tamerj1 Many thanks Sir, you safe my life. I modify your formula and got what I need !!
I got over 30 attrute and thousand of products and now I can dynamically screen out and provide the mapping to user, many thanks for your time !Mapping Ratio = VAR SelectedProduct = SELECTEDVALUE ( Products[Product] ) VAR SelectedTable = SELECTCOLUMNS ( FILTER ( ALL ( 'Table' ), 'Table'[Product] = SelectedProduct ), "A", 'Table'[AttributeA], "B", 'Table'[AttributeB], "C", 'Table'[AttributeC], "D", 'Table'[AttributeD] ) VAR CombinedTable = CROSSJOIN ( 'Table', SelectedTable ) VAR Matches = SUMX ( CombinedTable, VAR Condition1 = INT ( 'Table'[AttributeA] = [A] ) VAR Condition2 = INT ( 'Table'[AttributeB] >= [B] ) VAR Condition3 = INT ( 'Table'[AttributeC] >= [C] ) VAR Condition4 = INT ( 'Table'[AttributeD] = [D] ) RETURN Condition1 * ( Condition2 * ( Condition1 + Condition2 + Condition3 + Condition3 + Condition4 ) ) ) RETURN IF ( HASONEVALUE ( 'Table'[Product] ), DIVIDE ( Matches, 4 ) )
Many thanks Sir, it's helpful !!!
Still got one problem is that some attribute content is blank,
for example, if the filter ProductA and it doesn't have AttributeA&B,
Current result will become as below
| Product | AttributeA | AttributeB | AttributeC | AttributeD | Mapping Ratio |
| ProductA | Value4 | Text5 | 100% | ||
| ProductB | Text3 | Value1 | Value4 | Text1 | 25% |
| ProductC | Text2 | Value3 | Text3 | Text2 | 0% |
| ProductD | Text2 | Value3 | Value4 | Text5 | 50% |
However, I'd like to become below, when productA missing two attribute, the overall mapping ratio denominator should be ignore missing attribute then become 2.
May please help me ? appreciate !!!
| Product | AttributeA | AttributeB | AttributeC | AttributeD | Mapping Ratio |
| ProductA | Value4 | Text5 | 100% | ||
| ProductB | Text3 | Value1 | Value4 | Text1 | 50% |
| ProductC | Text2 | Value3 | Text3 | Text2 | 0% |
| ProductD | Text2 | Value3 | Value4 | Text5 | 100% |
Venson
This is a bit more comples
Mapping Ratio =
VAR SelectedProduct = SELECTEDVALUE ( Products[Product] )
VAR FilteredTable = FILTER ( ALL ( 'Table' ), 'Table'[Product] = SelectedProduct )
VAR SelectedTable =
FILTER (
SELECTCOLUMNS (
CROSSJOIN ( { "A", "B", "C", "D" }, FilteredTable ),
"Index", [Value],
"Attribute", SWITCH ( [Value], "A", 'Table'[AttributeA], "B", 'Table'[AttributeB], "C", 'Table'[AttributeC], "D", 'Table'[AttributeD] )
),
[Attribute] <> BLANK ( )
)
VAR CurrentTable =
SELECTCOLUMNS (
CROSSJOIN ( SELECTCOLUMNS ( SelectedTable, "@Index", [Index] ), 'Table' ),
"Index", [@Index],
"Attribute", SWITCH ( [@Index], "A", 'Table'[AttributeA], "B", 'Table'[AttributeB], "C", 'Table'[AttributeC], "D", 'Table'[AttributeD] )
)
VAR MatchedTable =
INTERSECT ( CurrentTable, SelectedTable )
RETURN
IF (
HASONEVALUE ( 'Table'[Product] ),
DIVIDE ( COUNTROWS ( MatchedTable ), COUNTROWS ( SelectedTable ) ) + 0
)Thank you tamerj1 !!
it's workable, but I think I have to improve attribute missing info not cover it in the end, so I choose your first solution for current work.
not only mapping ratio, but also I got one charlleng now is I have to only filter out some key attribute mapping panel.
May I know what should I add in the below return portion ?
highly appreciate!!
RETURN IF ( HASONEVALUE ( 'Table'[Product] ), Matches / 4 )tamerj1 Thank you Sir. yes, you're right, Let me put the example!
Database example
Product AttributeA AttributeB AttributeC AttributeD ProductA Text, A Value, 2 Value2 Text, B ProductB Text, A Value, 1 Value3 Text, B ProductC Text, A Value, 3 Value Text, B ProductD Text, A Value, 3 Value4 Text, C After filter A, rest of products compare to ProductA's attribute follow below conditions
Condition1: AttribueA must same & AttributeB value should larger than productA --> Count Mapping Ratio
Condition2: Same as AttributeB, if AttributeC value lager than productA treat it as mapped.
Condition3: AttributeD, text is the same treat it as mapped.
Product AttributeA AttributeB AttributeC AttributeD Mapping ratio ProductA Text, A Value, 2 Value, 2 Text, B 100% ProductB Text, A Value, 1 Value, 3 Text, B (blank) ProductC Text, A Value, 3 Value, 3 Text, B 100% ProductD Text, A Value, 3 Value, 1 Text, C 50%