Forum Discussion
Venson
3 years agoFrequent Visitor
Dynamic Attribute Mapping Measure
Hi everyone, Thanks for your times here ! I got a database as below. Product AttributeA AttributeB AttributeC AttributeD ProductA Text2 Value3 Value4 Text5 ProductB Text3 Valu...
- 3 years ago
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 ) ) - 3 years ago
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 ) )
tamerj1
3 years agoCommunity Champion
Venson
Not 100% clear. However, please try the following
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 )
)Venson
3 years agoFrequent Visitor
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 )
)