I have an interesting scenario, I have Created a Virtual Table & Would like to use userelationship or Treatas to override the natural physical relationship. In the attached PBIX i am getting answer based on the relationship Document date of order table and date from calendar. However i need to get the result based on the grdate which is a calculated column and the calendar date table.
I have also created a physical table out of the virtual table logic which i am trying to build in the measure just to give everyone clarity as how to calculate the Collection Lead time as per business scenario.
Looking forward to some different dax perspective which i have missed in order to resolve it.
One thing that I think would be helpful in getting a good response on this would be to provide a mockup of the result you are looking for. I read this one over twice, and still don’t have a clear sense of what the final objective is here.
The basic issue is with @grdate calculation. For calculating @grdate, the code is filtering Material Movement table that also have relationship with Calendar Table. When report is using Dates from Calendar table, this table is also filtered and if there are no records within the dates provided it is giving blank.
Can test for Purchasing Document 4500309048. Dates selected in slicer are till 31st July however for this “Posting Date” is 10th August, hence it is excluded while doing calculation using Calendar slicer.
So, either remove relationship b/w Calendar and Material Movement or change code to add all in Material Movement like
FILTER (
all('Material Movement')
PS: I am still getting difference b/w required values (18) and virtual values (21) earlier it was empty, You can test as the code is huge and I haven’t gone through all conditions.
Thing which i understood from this whole exercise of virtual table and calculated column within a virtual table is that if a calculated column is created in a virtual table it will hold on to the lineage of relationships it had in the parent table from where we have called it.
This is same thing happening with @grdate too. @grdate in a way belongs belongs to material movement table and hence if you call or use this as a calculated column in any other virtual table it will hold on to the lineage it had within material movement table.
There is another way of doing it if we somehow break the lineage of @grdate within virtual table and then force the calendar to use @grdate for filtering. The reason why it will work is because if we create @grdate and @rdte as physical column in order table first and then build our virtual table by summarizing order table than we will get the result we want to achieve.
This is how i have solved it. I created @grdate and @rdte in order table first as physical column and then did the summarization and userelationship. It worked perfectly.
I will wait for some other folks to verify this before closing this thread. Lets c may be there is another way to solve it.