Not able to get top 1 in power BI?

1
votes

  • Customer table

    contains the column "Status","created_date","updated date"

  • ascs table

    contains the column "service_station_name"

First I add new measure for "status count" by using following eqn:

    Status Count = COUNT('cps customers'[status])

then a new column for "day duration" by using following eqn:

     Day Duration2 = SWITCH('cps 
     customers'[status],"Closed",DATEDIFF('cps customers'[created_at], 
    [updated_at],DAY),"Unattended",DATEDIFF('cps 
    customers'[created_at],NOW(),DAY),"Assigned",DATEDIFF('cps 
    customers'[created_at],NOW(),DAY),"NotApplicable",DATEDIFF('cps 
    customers'[created_at],NOW(),DAY),"Open",DATEDIFF('cps 
    customers'[created_at],NOW(),DAY))

I need top service station name.. Conditions:

    i.   Consider only **closed status**
    ii.  Top count of **closed status count**
    iii. Lowest **time duration**

Steps:

  1. Select columns service_station_name,status,status count and day duration2 enter image description here

  2. Filter the closed status from column "status". enter image description here

  3. Add TOPN filteration for "status count" enter image description here

    Now, I got three service_station_name where each have 5 closed status count.

  4. And for getting the lowest "day duration2" I used ascending order enter image description here"

  5. And for displaying the top 1 service_station_name, I chosen the CARD from visualization and get First service_station_name.Bt I haven't got the proper answer enter image description here

  6. Then I took last service_station_name . Here also i haven't got the answer.enter image description here

Based on the screenshot, I need "Om Sakthi Engineering" as answer. How I get the answer? Can anybody help me!

1
powerbipowerbi-desktoppowerbi-mobile
Is there any way to get the answer by using "new measure" or "new column"... – Angel Reji

1 Answers

0
votes

You could actually add in another column "NewColumn", then the value would be the "Status Count" divided by "day Duration". The highest value will be the top one. 

NewColumn = divide(Table1[Status Count],Table1[Day Duration])

Then you can use card visualization to pick the top one value. Under filters, you should filter status as closed, filter the top 1 for this NewColumn. enter image description here