I bet this has been asked before and I should know the answer as well, but it just escapes me and I can't find it. So I tried to reproduce with the table below.
Column1 : Time Variable
Column2 : Product variable
Column3 : Quantity variable
Then I created the following measure:
@DouweMeer - Seems to me that you should create a disconnected table like this:
Table = DISTINCT('CurrentTable'[Time Value])
Or just use GENERATESERIES
Use that as your columns. Then you can grab the value of the current column using MAX/MAXX or SELECTEDVALUE and get everything <= that value from your other table. Should fix you right up.
Would it really need a disconnected table? Otherwise I see the consequence that you would be better off just create the whole table as a disconnected table and use its columns as filters :).
@DouweMeer I'm guessing because apparently:
@Greg_Deckler Nah, it's not the VAR.
Well @DouweMeer - nobody can be forced to take advice. See attached PBIX, it really is the VAR. I edited your measure to return a1 instead and you can see the results for yourself. So...
@Greg_Deckler , what I meant with 'Nah, it's not the VAR' is that by 'fixing' the VAR, you won't resolve the problem.
If I would remove the filter from your calculate expression by so:
@DouweMeer - Not entirely sure I read all of that or caught all of that. It's odd, most people come here to have their problems fixed, not to wax poetic or debate. I already told you how to fix your problem. So, here is a PBIX that implements the solution.
I'm looking for an understandig, not a practical solution. It is a piece of my problem that I filtered from my larger datamodel. I have roughly 10 filters in my dataset that intertwine with the result of my measure.
I think it is either your perspective of 'us' as users of the forum asking question that we're just looking for a fish rather than try to learn how to fish. Or perhaps it is a cultural barrier where you as an American are more an 'application-first' person than myself being more in the 'concept-first' perspective.
So far I've been asking for A, you said B but somehow telling me the solution for 'B' is 'B' while it is actually for 'A'. I don't know how you conceive our conversation, all I know is that I consider it as something that you agree with my about the underlining problem of Power BI.
All I see is that the consequence of this limitation would be that you need a shadow dataset in your datamodel with the same values and same relationships, but only used as filters to apply on the original values.
@DouweMeer - After a long bunch of words and things, the question from the original forum post was:
"How can I create the measure in such a way that for product variable 'B' at time variable '2' it will show the return value '3' and at time variable '6' the return value '18'?"
And the answer is that you use the disconnected table trick. I'm not sure what else to tell you. You can't simply invent data that isn't there unless you do something along the lines of the disconnected table trick where you effectively "invent" the rows of data that are missing from the dataset.
I think the issue is that I look at the problem and say. "Well of course you get that result, what the heck else do you expect? You don't have the data". And you look at the problem and somehow see issues with implicit filters and contexts and other things that, well, I honestly have no idea what you see as the problem. The problem is, you don't have the data. It's very simply, straight-forward and there is no mystery or complexity regarding it. And if you don't have the data, you need to create the data. And the tried and true method of doing that is the disconnected table trick.
Here is another case that you might find interesting regarding time gaps in data. https://community.powerbi.com/t5/Quick-Measures-Gallery/Mind-the-Gap-Irregular-Time-Series/m-p/99179...
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications
Features releasing from October 2020 through March 2021