I am trying to create a first in first out matrix table based on the lowest number of the traveler being on the bottom (Number 1)
See the attached screen shot of the matrix table. For each material number column, the table should have all the traveler numbers populated with the lowest number being at the bottom indicating its the oldest part and should be first out. Highest numbers on the top indicating that they where the last in.
I am almost there i have it working but i have blank spaces in my table which need to be gone and then were good. can anyone suggest where i am going wrong?
Hi,
Trying to get my head around this. Nice model by the way. Are you sure you are almost where you want to be? In your setup (example FIFO33) multiple material numbers show multiple traveler numbers.
When I apply ranking on either materialnumber or travelernumber I get different results, furthermore in tghe matrix you will always show blanks if the fifo and the number do not match.
In general your datamodel needs some work, assuming you wish to expand on the other tables, add a date table. Also make sure you “Mark as Date table”, otherwise PBI will create multiple date tables, 1 for each column holding a date, in the background, slowing your model down.
Basically i need to create the FIFO number (Calculated Column) on each material number. I think whats happening in my FIFO column is the FIFO rank number is being calculated on all traveler numbers and not creating ranking by material number. do i need to put another part to my code to make that happen? or instead of using a Calculated column how could i create a measure for this?
I do have a date table in there but its not used for anything currently.
Hi,
I have added a new table, PL LOB, that ranks (your FIFO) based on materialnumber, resulting in 64 distinct material numbers with the corresponding travelernumbers.
Probably a measure with a virtual table could do the same but this visualizes the calculation.
Sorry maybe i wasn’t clear in my description. I need the ranking to be an index as such so no duplicates (All traveller number are unique) and based on the lowest number being number 1. This shall be for each material.
No, it was not clear, basically I corrected on your initial fifo. An index is different to ranking, So you wish to create an index for each individual materialnumber to show 1…x for the traveler numbers.
This calls for a Power Query solution I think, grouping the materialnumbers and add an index for each one.
Paul
see attached example
.Rankx problem.pbix (23.6 KB) rankx problem.xlsx (22.1 KB)
Paul
