Forum Discussion

yharfush's avatar
yharfush
Frequent Visitor
4 years ago
Solved

New measure with string values from table

Hello,

 

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

 

STORE_IDPROD_TYPE
STORE_01A
STORE_01B
STORE_02B
STORE_03A
STORE_03B
STORE_04A
STORE_05B

 

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_IDMEASURE
STORE_01BOTH
STORE_01BOTH
STORE_02ONLY B
STORE_03BOTH
STORE_03BOTH
STORE_04ONLY A
STORE_05ONLY B

 

Any help would be greatly appreciated!

Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    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/

     

     

2 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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's avatar
      yharfush
      Frequent Visitor
      4 years ago


      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! 🙂