Forum Discussion
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:
2 Replies
- v-piga-msftResident 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
- fbottazzoliAdvocate V
Hi v-piga-msft and all,
please find attached the link to download a simplified version of my model: https://drive.google.com/file/d/19UI8Ee4qF3wRWB96ngZfkaDx0q0AReWN/view?usp=sharing
Thank you in advance for your help!
Francesca