Forum Discussion
Helper ILatest entry for distinct values in Power BI
Hi,
Is this the result you are expecting? You may download the file from here. Please note that i have removed the Status column. You had written a Status calculated column formula which in turn was refering a measure. A calculated column formula should not refer to a measure. If the result in the file is correct, then we can talk of the second problem i.e. getting the Status column in the Table with the help of a measure (not a calculated column).
Helper IHi Tom,
Thanks for the response. My issue is also related to month field in original table. As you said its in string format.
I tried to get the order of months with a copy of KPI table, where I converted these months to date format. My original datasource is on Sharepoint, so I couldnt change the dates in Original Table. As a result I get a copy of this table, open in excel, and added two fields 2 show monts. 1-Date format 2-numbers from 1 to 12.
But I couldnt handle the max value with that way. When I want to put a filter on different table, I receive a circular dependency error.
What exactly I need, is to get the last Ontrack Cumulative value in this table.
So I thought, If I can get the latest entry for each distinct deliverable, I can sort out. However It wasnt successfull.
I uploaded the pbix example with example data on dropbox:
https://www.dropbox.com/s/wx3hcklr860c33s/Calculate%20Ontrack%20based%20on%20Last%20Entry.pbix?dl=0
Super User- ruyaselman
Hi Ashish,
Thank you so much... This is what I`m looking for actually. However when I tried in original source, I couldnt sort it with that way.
You succesfully get the latest month , however I could be able to get the relevant month info for non-empty fields.
Now I`m adding the original source that I worked on; kindly check;
https://www.dropbox.com/s/o62aoapb871b76i/sample-2.pbix?dl=0
- Ashish_Mathur
You have completed changed the question. In the original file you shared, there was a relationship from the KPIOrj table to the CopyKPI table - now there is no such relatioship. If your final relatioships are the ones that you have shown in your sample2 file, then there must be a date column in the KPI table.
Also, please share the final result you want.
- ruyaselman
Hi Ashish,
I checked the relation in the second file, I have a relation from Copy Kpi - Original KPI. I couldn`t get the point that you mentioned. Only names of tables are different in sample.
Should I have a relation from KPI - Copy Kpi? Does it make a difference if I switch them?
Actually my main problem was the month field in KPI table, which is string format.
Although today sharepoint developer added a new column in KPI Table, which is in date format, still in powerBi I can not convert this column to a date... PowerBi realize as a text... Because of this issue, I get a copy of KPI table and in excel manually I added a month column in date format. With that way, PowerBI understand as date. Strange..
As a second option, I wanted to add a column in KPI Table, however I couldnt replace January with a date.
Maximum point that I reached is to replace January with numbers like 1-2-3. Do you have a suggestion for converting to date?
As a method I tried,
Measure =SWITCH(KPI[MonthNumber],1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")
But this is not helping me to sort the months properly in the table. I also tried to switch dates with mmddyyyy format, but it didnt work....
In the example that I sent to you, my main aim is to get the latest OnTrack Value...So what I thought, If I can get the latest entry for each ID like 160, 164 etc based on the last entered achievement, I can find out the corresponding On Track Value. And make my calculations.
Kindly find below example output;
Many thanks for the support