Distinct count based on the two columns

[Saxon10]

This is sales data. If same item has dispatched multiple country then result is Yes and if not then No.

I search in Power BI forum and I can't found any similer thread so could you please advise.

Item	Area Code	Desired Result
123	Ind	Yes
123	Uk	Yes
123	us	Yes
123	Aus	Yes
124	Ind	No
124	Ind	No
124	Ind	No
125	Ind	Yes
125	Uk	Yes
126	Us	No
127	Aus	No

[jdbuchanan71 · Accepted solution]

@Saxon10 

Try it like this

Multiple Areas = 
VAR _Areas =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Area Code], 'Table'[Item] ),
        REMOVEFILTERS ( 'Table'[Area Code] )
    )
VAR _Result =
    COUNTROWS ( _Areas )
RETURN
    IF ( _Result > 1, "Yes", "No" )

You could also use this as a measure instead of a calculated column.

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=if(calculate(distinctcount(Data[Area code]),filter(Data,Data[Item]=earlier(Data[Item])))>1,"Yes","No")

Hope this helps.

[Jihwan_Kim · Accepted solution]

Hi, @Saxon10 

Please check the below for creating a new measure.

Disinct Count Measure =
VAR currentareacode =
MAX ( 'Table'[Area Code] )
VAR currentitem =
MAX ( 'Table'[Item] )
VAR filtercondition =
FILTER ( ALL ( 'Table' ), 'Table'[Area Code] <> currentareacode )
VAR tablewithoutcurrentareacode =
SUMMARIZE ( filtercondition, 'Table'[Item] )
VAR result =
IF ( currentitem IN tablewithoutcurrentareacode, "Yes", "No" )
RETURN
IF ( ISFILTERED ( 'Table'[Item] ), result )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=if(calculate(distinctcount(Data[Area code]),filter(Data,Data[Item]=earlier(Data[Item])))>1,"Yes","No")

Hope this helps.

[jdbuchanan71 · Accepted solution]

@Saxon10 

Try it like this

Multiple Areas = 
VAR _Areas =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Area Code], 'Table'[Item] ),
        REMOVEFILTERS ( 'Table'[Area Code] )
    )
VAR _Result =
    COUNTROWS ( _Areas )
RETURN
    IF ( _Result > 1, "Yes", "No" )

You could also use this as a measure instead of a calculated column.