topic Re: Dynamic Attribute Mapping Measure in DAX Commands and Tips

Dynamic Attribute Mapping Measure

Venson — Sun, 02 Jul 2023 13:27:44 GMT

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%

Re: Dynamic Attribute Mapping Measure

tamerj1 — Sun, 02 Jul 2023 15:20:42 GMT

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 )

Re: Dynamic Attribute Mapping Measure

Venson — Sun, 02 Jul 2023 16:37:33 GMT

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%

Re: Dynamic Attribute Mapping Measure

tamerj1 — Sun, 02 Jul 2023 17:35:09 GMT

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 )

Re: Dynamic Attribute Mapping Measure

Venson — Mon, 03 Jul 2023 05:28:55 GMT

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 )

Re: Dynamic Attribute Mapping Measure

tamerj1 — Mon, 03 Jul 2023 06:29:37 GMT

Venson
Please clarify further perhaps using some examples.

Re: Dynamic Attribute Mapping Measure

Venson — Mon, 03 Jul 2023 08:08:28 GMT

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%

Re: Dynamic Attribute Mapping Measure

tamerj1 — Mon, 03 Jul 2023 11:16:56 GMT

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

Re: Dynamic Attribute Mapping Measure

Venson — Mon, 03 Jul 2023 14:23:12 GMT

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