Forum Discussion

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

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 )
    )

Community 652.pbix44 KB

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 )
    )

tamerj1

Community Champion

3 years ago

Venson
Please clarify further perhaps using some examples.

Venson

Frequent Visitor

3 years ago

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%

tamerj1

Community Champion

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 )
    )

Community 652.pbix44 KB

Venson

Frequent Visitor

3 years ago

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 )
    )

Forum Discussion

Dynamic Attribute Mapping Measure

Related content

Copy activity dynamic mapping

Order attributes

Dynamic Filter

Attribute Slicer Continuation

Filled map