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).
Super UserHey,
it would be helpful if you create a pbix with sample data, upload the file to onedrive or dropbox and share the link.
I assume you create a calculated column?
You receive March for all you deliverables, because your current DAX takes not the single Deliverable into account. This could be done something like this
name of the column =
CALCULATE(
MAX('tablename'[Month])
,FILTER(
ALLEXCEPT('tablename', 'tablename'[DeliverableID])
,'tablename'[Achieved] <> BLANK()
)But you also have to keep in mind that MAX() from a string column returns March instead of April due to to alphabetically ordering.
I assume that you have to do the following
- Determine the MAX ID (assuming that the ID is a number that reflects a sequence and is unique across all deliverables) for each deliverable
- use the max ID to determine the value for the Last Entry column, maybe using the function LOOKUPVALUE()
https://msdn.microsoft.com/en-us/library/gg492170.aspx
If there is no sustainable Business Rule to determine the order of IDs, calculating a cumulative value will not be possible. You also might consider to transform the Month column in a real date value: 2018-02-01 represents February in the year 2018 and 2017-01-01 represents January in the year 2017
Hopefully this gives you an idea
Kind regards
Tom
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
- Ashish_Mathur
- 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.