Match and count categories

[alevandenes]

Hi community,

I have a problem that i dont know how to approach. I hope you can help me out with solving it.

I would like for a count to be set up when specific characteristics are matched.

in the example below, i would like for PowerBI to tell me that 2 orders are completely matching.

order #	Product	Colour	size
1342526	Shoes	Red	37
2342634	Shoes	Red	37
3476529	Belt	Red	Small

Any ideas?

i was thinking of using keys for the different combinations, but they could be infinite

Thank you in advance,
Alessandra

[Ashish_Mathur]

Hi,

A simple solution would be to create a calculated column formula to concatenate product, colour and size.  Then write a measure to do a distincount of the entries in the new column.

Hope this helps.

[DataInsights · Accepted solution]

Hi @alevandenes,

Try this measure:

Match Count = 
VAR vProduct =
    MAX ( Orders[Product] )
VAR vColour =
    MAX ( Orders[Colour] )
VAR vSize =
    MAX ( Orders[size] )
VAR vTable =
    FILTER (
        ALLSELECTED ( Orders ),
        Orders[Product] = vProduct
            && Orders[Colour] = vColour
            && Orders[size] = vSize
    )
VAR vResult =
    COUNTROWS ( vTable )
RETURN
    vResult