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

     

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

        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
    )