cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## How can I calculate the number (Count) of similar products?

I have this measure to Select Product and show similar products based on multiple Criteria, but i cant calculate cout of similar Product.

``````_ =
VAR __c =
CALCULATETABLE(
SUMMARIZE( Data, slicerCriteria[Attr], Data[Value] ),
REMOVEFILTERS( Data[Product] )
)
RETURN
IF(
ISEMPTY(
EXCEPT(
CALCULATETABLE(
SUMMARIZE( Data, Data[Attribute], Data[Value] ),
REMOVEFILTERS( slicerProduct[Prod] )
),
__c
)
),
""
)``````

Data-Table:

ProductAttributeValue
 A Color Green A Size 1 A Cost 1000 A Marked Alex B Color Red B Size 2 B Cost 4000 B Marked Martin C Color Green C Size 1 C Cost 1000 C Marked Martin D Color Blue D Size 5 D Cost 2000 D Marked Sam E Color Red E Size 2 E Cost 4000 E Marked Laura F Color Green F Size 1 F Cost 1000 F Marked Alex G Color Green G Size 5 G Cost 11000 G Marked Caro

slicerCriteria:

Attr
 Color Size Cost Marked

slicerProduct:
Prod

 A B C D E F G

1 ACCEPTED SOLUTION
Community Support

It's quite a complicated process to achieve your desired results.
First, you need to remove all relationships between tables.

Create these measures:

``````Measure 3 = VAR a=SUMMARIZE(FILTER(ALLSELECTED(Data),[Product] IN VALUES(slicerProduct[Prod])&&Data[Attribute] IN VALUES(slicerCriteria[Attr])),[Attribute],Data[Value])
var b=CONCATENATEX(a,[Attribute],",")
var c=CONCATENATEX(a,[Value],",")
//var d=SUMMARIZE(FILTER(ALLSELECTED(Data),NOT([Product] IN VALUES(slicerProduct[Prod]))&&Data[Attribute] IN VALUES(slicerCriteria[Attr])),[Product],Data[Attribute],Data[Value])
//var e=ADDCOLUMNS(d,"test1",CONCATENATEX(FILTER(d,[Product]  in VALUES(Data[Product])),[Attribute],","),"test2",CONCATENATEX(FILTER(d,[Product] in VALUES(Data[Product])),[Value],","))
return b``````
``````Measure 4 = VAR a=SUMMARIZE(FILTER(ALLSELECTED(Data),[Product] IN VALUES(slicerProduct[Prod])&&Data[Attribute] IN VALUES(slicerCriteria[Attr])),[Attribute],Data[Value])
var b=CONCATENATEX(a,[Value],",")
return b``````
``````Measure 5 = var a=SUMMARIZE(FILTER(ALLSELECTED(Data),NOT([Product] IN VALUES(slicerProduct[Prod]))&&Data[Attribute] IN VALUES(slicerCriteria[Attr])),[Product],Data[Attribute],Data[Value])
var b=CONCATENATEX(FILTER(a,[Product]  in VALUES(Data[Product])),[Attribute],",")
return b``````
``````Measure 6 = var a=SUMMARIZE(FILTER(ALLSELECTED(Data),NOT([Product] IN VALUES(slicerProduct[Prod]))&&Data[Attribute] IN VALUES(slicerCriteria[Attr])),[Product],Data[Attribute],Data[Value])
var b=CONCATENATEX(FILTER(a,[Product] in VALUES(Data[Product])),[Value],",")
return b``````
``Measure 7 = IF(ISFILTERED(slicerProduct[Prod])||ISFILTERED(slicerCriteria[Attr]),IF([Measure 5]=[Measure 3]&&[Measure 6]=[Measure 4]&&SELECTEDVALUE('Data'[Attribute]) in VALUES(slicerCriteria[Attr]),1,0),1)``

Make the settings as shown in the following figure:

Then create this measure:

``Measure = DISTINCTCOUNT(Data[Product])``

Put this measure into the table visual, and the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Frequent Visitor

Thank you😀

Community Support

It's quite a complicated process to achieve your desired results.
First, you need to remove all relationships between tables.

Create these measures:

``````Measure 3 = VAR a=SUMMARIZE(FILTER(ALLSELECTED(Data),[Product] IN VALUES(slicerProduct[Prod])&&Data[Attribute] IN VALUES(slicerCriteria[Attr])),[Attribute],Data[Value])
var b=CONCATENATEX(a,[Attribute],",")
var c=CONCATENATEX(a,[Value],",")
//var d=SUMMARIZE(FILTER(ALLSELECTED(Data),NOT([Product] IN VALUES(slicerProduct[Prod]))&&Data[Attribute] IN VALUES(slicerCriteria[Attr])),[Product],Data[Attribute],Data[Value])
//var e=ADDCOLUMNS(d,"test1",CONCATENATEX(FILTER(d,[Product]  in VALUES(Data[Product])),[Attribute],","),"test2",CONCATENATEX(FILTER(d,[Product] in VALUES(Data[Product])),[Value],","))
return b``````
``````Measure 4 = VAR a=SUMMARIZE(FILTER(ALLSELECTED(Data),[Product] IN VALUES(slicerProduct[Prod])&&Data[Attribute] IN VALUES(slicerCriteria[Attr])),[Attribute],Data[Value])
var b=CONCATENATEX(a,[Value],",")
return b``````
``````Measure 5 = var a=SUMMARIZE(FILTER(ALLSELECTED(Data),NOT([Product] IN VALUES(slicerProduct[Prod]))&&Data[Attribute] IN VALUES(slicerCriteria[Attr])),[Product],Data[Attribute],Data[Value])
var b=CONCATENATEX(FILTER(a,[Product]  in VALUES(Data[Product])),[Attribute],",")
return b``````
``````Measure 6 = var a=SUMMARIZE(FILTER(ALLSELECTED(Data),NOT([Product] IN VALUES(slicerProduct[Prod]))&&Data[Attribute] IN VALUES(slicerCriteria[Attr])),[Product],Data[Attribute],Data[Value])
var b=CONCATENATEX(FILTER(a,[Product] in VALUES(Data[Product])),[Value],",")
return b``````
``Measure 7 = IF(ISFILTERED(slicerProduct[Prod])||ISFILTERED(slicerCriteria[Attr]),IF([Measure 5]=[Measure 3]&&[Measure 6]=[Measure 4]&&SELECTEDVALUE('Data'[Attribute]) in VALUES(slicerCriteria[Attr]),1,0),1)``

Make the settings as shown in the following figure:

Then create this measure:

``Measure = DISTINCTCOUNT(Data[Product])``

Put this measure into the table visual, and the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors