New measure with string values from table

[yharfush]

Hello,

I have a table which contains sales by store and by product type (A or B):

STORE_ID	PROD_TYPE
STORE_01	A
STORE_01	B
STORE_02	B
STORE_03	A
STORE_03	B
STORE_04	A
STORE_05	B

I need to create a measure which will tell me if each store sells Only Prod A, Only Prod B or Both (it would look like this):

STORE_ID	MEASURE
STORE_01	BOTH
STORE_01	BOTH
STORE_02	ONLY B
STORE_03	BOTH
STORE_03	BOTH
STORE_04	ONLY A
STORE_05	ONLY B

Any help would be greatly appreciated!

[VahidDM · Accepted solution]

Hi @yharfush 

Try this code to create your measure:

Measure = 
VAR _A =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[PROD_TYPE] ),
        ALLEXCEPT ( 'Table', 'Table'[STORE_ID] )
    )
RETURN
    IF ( _A = 2, "Both", "Only " & MAX ( 'Table'[PROD_TYPE] ) )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[yharfush]

Thank you so much! This works perfectly.

If I can ask another thing, how can I add a filter to the table? Because I have two different time periods for my data and I need to show the results of only one period. I tried this but it doesn't seem to be working:

Measure = 
VAR _A =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[PROD_TYPE] ),
        ALLEXCEPT ( 'Table', 'Table'[STORE_ID] ),
        FILTER( 'Table', 'Table'[PERIOD] = "PERIOD1" )
    )
RETURN
    IF ( _A = 2, "BOTH", "ONLY " & MAX ( 'Table'[PROD_TYPE]  ))

Thanks again! 🙂