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 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
Super UserYou 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
- Ashish_Mathur
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).
- ruyaselman
Hi Ashish,
This is what I was looking for thank you so much..
I also find a way to add a new column and switch it to date format. Maybe it will be usefull for end users, I used the following code:
Column = SWITCH('KPI'[Month],"January",DATE(2018,01,01),"February",DATE(2018,02,01),"March",DATE(2018,03,01),"April",DATE(2018,04,01),"May",DATE(2018,05,01),"June",DATE(2018,06,01),"July",DATE(2018,07,01),"August",DATE(2018,08,01),"September",DATE(2018,09,01),"October",DATE(2018,10,01),"November",DATE(2018,11,01),"December",DATE(2018,12,01))
Many thanks for your support