Forum Discussion
Dynamic target in measures
Hello All,
I need a help with a dynamic target and according to this target I would like to calcualte a measure. Let me give you some example.
I have a list of countries and each of them have their own target, for example Germany - 3 days , Spain - 4 days to process the ticket.
The measure needs to count how many tickets were closed within this target. So I tried something like this for Germany : Tickets within KPI = Calculate(Count(Table1[Column1]),(Table1[Days]) <= 3) and it works fine but unfortunately I have a lot of the countries and I would like to avoid creation that many measurments with different target.
Is there is any way to create a dymanic target so when I pick up in the slicer Germany the Tickets within KPI measure will automatically count a rows with days below or equal 3 and when I choose Spain it will count rows with the 4 days below or equal ? I tried to created a table and used SWITCH function :
Dynamic Target = SWITCH(Targets[Country]),
2 "Germany", 3,
3 "Spain", 4)
but when try to use it in the measure : Tickets within KPI = Calculate(Count(Table1[Column1]),(Table1[Days]) <= [Dynamic Target] ) I have a warning message " A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
I am not Power BI expert so may thinking may look stupid for you but I will aprreciate any help. Thanks !
Hi, Niunias
I am not sure how your data model and tables look like.
I assume you can create a target table like below.
Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.
Tickets within Target =CALCULATE (COUNTROWS ( Tickets ),FILTER ( Tickets, Tickets[Days] <= SELECTEDVALUE ( Targets[Target] ) ))Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
2 Replies
- Jihwan_KimSuper User
Hi, Niunias
I am not sure how your data model and tables look like.
I assume you can create a target table like below.
Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.
Tickets within Target =CALCULATE (COUNTROWS ( Tickets ),FILTER ( Tickets, Tickets[Days] <= SELECTEDVALUE ( Targets[Target] ) ))Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
- NiuniasFrequent Visitor
Brilliant ! Thanks a lot 🙂 You have saved my weekend 🙂