Forum Discussion

Niunias's avatar
Niunias
Frequent Visitor
5 years ago
Solved

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.


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

2 Replies

  • 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.


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

  • Niunias's avatar
    Niunias
    Frequent Visitor

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