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_ID PROD_TYPE STORE_01 A STORE_01 B STORE_02 B STORE_03 A STORE_03 B STORE_04...
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/

     

     

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