Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
1 | A | 3 |
2 | A | 8 |
3 | A | 7 |
4 | A | 12 |
5 | A | 5 |
6 | A | 1 |
7 | A | 18 |
1 | B | 3 |
3 | B | 5 |
4 | B | 4 |
5 | B | 6 |
7 | B | 9 |
Then I created the following measure:
Sum =
@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:
Sum =
@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...
Thank you for sticking with this question and the other case is an insight that is very interesting.
This weekend over a barbeque I had a discussion with my brothers about the topic as well and the brother who is in Engineering told me he was 100% certain that the 'issue' I described was an issue on wrongful input which, and he was adamantite, should be resolved by a '0 matrix'. Do you have experience with a '0 matrix' and know whether that principle could be applied in Power BI to fix the issue on hand?
For the rest I can only acknowledge that the lack of understanding of the engine behind it is holding me back and it looks like I need a course in it. Do you perhaps have a suggestion what I could follow to get a better understanding of this (is that Vertipaq, DAX, or something else)?
@DouweMeer - A zero matrix is just a matrix of all zeros. I'm not sure how it applies to this situation. However, I can tell you that DAX is generally not suited for matrix algebra. See MMULT as an example. https://community.powerbi.com/t5/Quick-Measures-Gallery/MMULT/m-p/630231#M315
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |