Dynamic Attribute Mapping Measure

[Venson]

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	Value1	Value4	Text1
ProductC	Text2	Value3	Value3	Text2
ProductD	Text2	Value3	Value4	Text5

I'd like to have a measure result table, when apply slicer filter ProductA, it will be adding one mapping Ratio column make a product compare ProductA and calculate the Mapping Ratio, if all attribute same as ProductA it will be 100%.

May please help me ? Many thanks !

Product	AttributeA	AttributeB	AttributeC	AttributeD	Mapping Ratio
ProductA	Text2	Value3	Value4	Text5	100%
ProductB	Text3	Value1	Value4	Text1	25%
ProductC	Text2	Value3	Value3	Text2	50%
ProductD	Text2	Value3	Value4	Text5	100%

[tamerj1 · Accepted solution]

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

[tamerj1]

Hi @Venson 
Please refer to attached sample file with the proposed solution

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,
        INT ( 'Table'[AttributeA] = [A] )
            + INT ( 'Table'[AttributeB] = [B] )
            + INT ( 'Table'[AttributeC] = [C] )
            + INT ( 'Table'[AttributeD] = [D] )
    )
RETURN
    IF ( 
        HASONEVALUE ( 'Table'[Product] ), 
        Matches / 4
    )

[Venson]

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%

[tamerj1]

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

[Venson]

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]

@Venson 
Please clarify further perhaps using some examples.

[Venson]

@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 · Accepted solution]

@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 · Accepted solution]

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