Forum Discussion

Parth_1's avatar
Parth_1
Helper I
2 years ago
Solved

Not Able to apply condition while performing a calculation

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
 
 
 
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Parth_1
      Helper I
      2 years ago

      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")

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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!