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.

ProductAttributeAAttributeBAttributeCAttributeD
ProductAText2Value3Value4Text5
ProductBText3Value1Value4Text1
ProductCText2Value3Value3Text2
ProductDText2Value3Value4Text5

 

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 !

 

ProductAttributeAAttributeBAttributeCAttributeDMapping Ratio
ProductAText2Value3Value4Text5100%
ProductBText3Value1Value4Text125%
ProductCText2Value3Value3Text250%
ProductDText2Value3Value4Text5100%

 

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

     

8 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      Venson
      Frequent Visitor
      3 years ago

      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

      ProductAttributeAAttributeBAttributeCAttributeDMapping Ratio
      ProductA  Value4Text5100%
      ProductBText3Value1Value4Text125%
      ProductCText2Value3Text3Text20%
      ProductDText2Value3Value4Text550%

       

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

      ProductAttributeAAttributeBAttributeCAttributeDMapping Ratio
      ProductA  Value4Text5100%
      ProductBText3Value1Value4Text150%
      ProductCText2Value3Text3Text20%
      ProductDText2Value3Value4Text5100%
      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        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
    )