Dynamic Targets Using Parameters

[SK09]

Hi

I have request from a user’s that he want to enter the target value into a parameter and the target value should be compared with the data and the output on the visual should show if the target is reached or not with in the selected date range

Below is the transaction data

in the below visuals if the users enter the target value inside the parameter the Depo_Check column should check if the target amount is reached or not for the selected date ranges provided in the date slicer.

Can anyone help me on creating a measure which allow me to get the desired output?

Thank you in advance.

Sai

[Anonymous]

Hi @SK09 ,

Please try:

Measure = 
VAR _parameter = MAX('Parameter'[Parameter])
VAR _count = 
COUNTROWS(
    FILTER(
        'Table',
        'Table'[Sum]>=_parameter
    )
)
VAR _result =
IF(_count>=1,"Yes","No")
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[SK09]

Hi Gao,

Thak you for your response.

I have tried the measure which you shared.

But my issue is that we have a relationship between the Table and Calender table and when i select the date range in the slicer as 01/May/2019 to 05/May/2019 and Pass a parameter values as 2000 (As shown in the screenshot below) 309 Appid ahoud return the value as "Yes" as it has a payment of 2000 on 02/May/2019.

For the same date range if enter the parameter value as 2100 or more the measure value should return "No" as it has a payment of only 2000 between that date range.

Attaching the screenshot for your reference.

Thank you in advance.

Sai

[Anonymous]

Hi @SK09 ,

My mistake, I forgot to create the relationship. After creating the relationship, it should output the correct result.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[SK09]

Hi Gao, 

I cannot create a direct relationship Between Table and Calender table.

Attaching the relationship diagram for your reference (Red box indicates inactive relationships and blue box indicates active relationships ) along with tha sample data.

AdmData.

ID.App     Date.Intake    sno              Date.App    Brand       Join

309	08/10/2018	Q1000304	11/07/2018	BSBI	BSBI|309
61231	03/10/2022	Q1000304	05/08/2022	BSBI	BSBI|61231

Table Data
Brand   sno                app id  nationality  Final.Date   EUR        CAD   Paid       Join          Sum

BSBI	Q1000304	309	India	07-Mar-19	1500	0	1500	BSBI|309	1500
BSBI	Q1000304	309	India	02-May-19	2000	0	2000	BSBI|309	3500
BSBI	Q1000304	309	India	13-May-19	100	0	100	BSBI|309	3600
BSBI	Q1000304	309	India	18-Nov-19	-100	0	-100	BSBI|309	3500
BSBI	Q1000304	309	India	22-Nov-19	0	0	0	BSBI|309	3500
BSBI	Q1000304	61231	India	01-Sep-22	1000	0	1000	BSBI|61231	1000
BSBI	Q1000304	61231	India	02-Sep-22	500	0	500	BSBI|61231	1500
BSBI	Q1000304	61231	India	10-Oct-22	500	0	500	BSBI|61231	2000
BSBI	Q1000751	9090	Pak	07-Mar-19	500	0	500	BSBI|9090	500
BSBI	Q1000751	9090	Pak	02-May-19	500	0	500	BSBI|9090	1000

[Anonymous]

Hi @SK09 ,

Please try:

Measure = 
VAR _parameter = MAX('Parameter'[Parameter])
VAR _min_date = MIN('Calendar'[Date])
VAR _max_date = MAX('Calendar'[Date])
VAR _count = 
COUNTROWS(
    FILTER(
        'Table',
        'Table'[Sum] >= _parameter
        && 'Table'[Paid.Date] >= _min_date
        && 'Table'[Paid.Date] <= _max_date
    )
)
VAR _result =
IF(_count>=1,"Yes","No")
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[sturlaws]

Hi, @SK09,

not sure what you are trying to achieve, I made a simple sample report, is this what you are after?


Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

How to Get Your Question Answered Quickly