Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Aladin66
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
            )
        ),
        ""
    )

 

2.png

Data-Table:

ProductAttributeValue
AColorGreen
ASize1
ACost1000
AMarkedAlex
BColorRed
BSize2
BCost4000
BMarkedMartin
CColorGreen
CSize1
CCost1000
CMarkedMartin
DColorBlue
DSize5
DCost2000
DMarkedSam
EColorRed
ESize2
ECost4000
EMarkedLaura
FColorGreen
FSize1
FCost1000
FMarkedAlex
GColorGreen
GSize5
GCost11000
GMarkedCaro

slicerCriteria:

Attr
Color
Size
Cost
Marked

slicerProduct:
Prod

A
B
C
D
E
F
G

1.png

1 ACCEPTED SOLUTION
v-junyant-msft
Community Support
Community Support

Hi @Aladin66 ,

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

vjunyantmsft_0-1707813326263.png

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:

vjunyantmsft_1-1707813518063.png

Then create this measure:

Measure = DISTINCTCOUNT(Data[Product])

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

vjunyantmsft_2-1707813595985.png


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.

View solution in original post

2 REPLIES 2
Aladin66
Frequent Visitor

Thank you😀

v-junyant-msft
Community Support
Community Support

Hi @Aladin66 ,

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

vjunyantmsft_0-1707813326263.png

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:

vjunyantmsft_1-1707813518063.png

Then create this measure:

Measure = DISTINCTCOUNT(Data[Product])

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

vjunyantmsft_2-1707813595985.png


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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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