Forum Discussion

Venson's avatar
Venson
Frequent Visitor
3 years ago
Solved

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...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
    Venson
    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 )
        )