cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Niunias
Frequent Visitor

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 !

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super 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.

 

Picture14.png

 

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.


Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Niunias
Frequent Visitor

Brilliant ! Thanks a lot 🙂 You have saved my weekend 🙂

Jihwan_Kim
Super User
Super 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.

 

Picture14.png

 

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.


Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors