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

[Aladin66]

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

[Aladin66]

Thank you😀

[Anonymous · Accepted solution]

Hi @Aladin66 ,

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.