Forum Discussion

fbottazzoli's avatar
fbottazzoli
Advocate V
7 years ago

Create dynamic label for Customer based on page filter

Hi all, 

I've a model with the following entities:

  • Customer DIM, including customer acquisition date
  • Sales Fact, with all the purchases made by the Customers
  • Calendar DIM, that is linked to Sales Fact.

On the Customer DIM (which is not directly join to Calendar) I created a dynamic column reporting the year of acquisition for each customer: Year of acquisition = YEAR(CUSTOMERS[CREATION_DATE].[Date])

 

In the report page I put a filter object allowing to select the Year from the Calendar. I configured the filter for only allowing single item selection.


What I want to do is to calculate dynamically on Customer level a label telling me about the state of the Customer in that year: New Customer (if the Customer was created in that year) or Existing. I would like to use the filter to compare sales made by New and Old Customers. Of course, the same Customer could have a different label based on the applied filters. 

 

I tried creating a measure like this for giving me the selected year: 

Selected year = IF(HASONEVALUE(CALENDAR[YEAR]);VALUES(CALENDAR[YEAR]);YEAR(TODAY()))

and tried to create a measure in the Customer DIM like this:

Label = IF(CUSTOMERS[Year of acquisition]=[Anno selezionato];"NEW CUSTOMER";"EXISTING")
 
But this doesn't give me the expected result.. Do you have any idea on what to do? 
Thank you in advance for your help.
Francesca 

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar

    Hi fbottazzoli ,

    More details will help us understand your sceanrio better.

    If it is convenient, could you show your sample data in text-tabular format.That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

    Best Regards,

    Cherry