Not Able to apply condition while performing a calculation

[Parth_1]

Hello Guys,

I am stuck at this, I am doing a project  where I have 5 columns(facility id, harvest id,

last_ modified(date), plantCount, source) and based onthat I want to calculate total Active plants in harvets.

for this we have reffered one SQL query 

Where,

facility_id = has different facility id's (in this case I need to choose 7),

harvest_id = had multiple, (repaeating harvest Id's),

last_modified = gives the last modified date for that harvest id,

PlantCount = gives total active plants on that date for facility_id and harvest_id.

Source = shows "Active" or "Inactive", want to choose "active" in this case

scenario (1) is, I want to find max(last_modified) based on a condition where,

facility_id & harvest_id are fixed.

Which function should I use to achieve this Fixed condition?

(I have treid GroupBy and Summarize But its not working in my case)

scenario (2) is, use this Max of last modified in total Active plants in harvest,

and put another condition , Source = "Active"

I am referring the Postgres query Follows:- 

(I need to perform this calculations in PowerBI)

-------------------------------------------------------------

select

    source,

    sum(plant_count :: numeric) :: int as plants,

    count(*) :: int

  from

    (

      select

        harvest_id,

        last_modified,

        source,

        data ->> 'PlantCount' as plant_count

      from

        harvests o

      where

        facility_id = 7

        and last_modified = (

          select

            max(last_modified)

          from

            harvests i

          where

            i.facility_id = o.facility_id

            and i.harvest_id = o.harvest_id

            and i.last_modified < '2022-07-28T23:59:59Z'

        )

    ) latest

  group by

    source

-----------------------------------------------------------------

Output Generated by query:- 

The Required output is Total Active plants in harvest =1737

Required Output is 1737 which is total active plants in harvest

[v-jingzhan-msft]

Hi @Parth_1 

If you want to calculate the desired result on the report level and also need to keep all other data in the model, you can use DAX to calculate the result. Here is a similar thread for your reference.

How to get value from last date (most recent) by c... - Microsoft Fabric Community 

If your purpose is to only query the desired result data into Power BI, some data sources support providing native queries when querying data through the built-in connector. For example, if your data source is PostgreSQL, you can use your current PostgreSQL statement to query the data. 

Import data from a database using native database query - Power Query | Microsoft Learn

Let me know if you have any questions. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Parth_1]

Hello v-jingzhan-msft,
Thanks for the reply.
I referred the link you provided,  And I have similar condition to apply But here in case I have to pass 2 columns which are (harvests[facility_id] && harvests[harvest_id]) which contains dynamic values in place of [(Topic] ="A" && [type]="AC")

[v-jingzhan-msft · Accepted solution]

Hi @Parth_1 

If you will add this calculation along with facility_id and harvest_id in the same visual, you can use a simpler measure like below to get the value of the latest modified date for each pair of facility_id and harvest_id. Columns in the same visual will pass these filters to the measure dynamically. 

Latest Count = 
var maxDate = MAX('Table'[last_ modified])
return
CALCULATE(SUM('Table'[plantCount]),'Table'[last_ modified]=maxDate, 'Table'[source]="Active")

If you use slicers or filters to choose facility_id and harvest_id dynamically, above measure also works. However it will also be affected by other filters if any. 

Or you can use 

Latest Count 2 = 
Var _maxdate=CALCULATE(MAX('Table'[last_ modified]),FILTER(ALL('Table'),[facility id]=SELECTEDVALUE('Table'[facility id])&&[harvest id]=SELECTEDVALUE('Table'[harvest id])))
Return
CALCULATE(MAX('Table'[plantCount]),FILTER(ALL('Table'),[last_ modified]=_maxdate&&[facility id]=SELECTEDVALUE('Table'[facility id])&&[harvest id]=SELECTEDVALUE('Table'[harvest id])))

SELECTEDVALUE function will get the value for you dynamcially. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!