cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Continued Contributor
Continued Contributor

YTD with no context

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

 

1A3
2A8
3A7
4A12
5A5
6A1
7A18
1B3
3B5
4B4
5B6
7B9

 

Then I created the following measure:

 

Sum =

VAR a1 = max ( 'Test table'[Time Variable] )
RETURN
calculate (
   sumx ( filter ( 'Test table' , 'Test table'[Time Variable] <= a1 ) , [Quantity variable] )
   , all ( 'Test table'[Time Variable] )
   )
 
Now the problem, when plotting this measure in a Matrix...
Untitled.png
 
The following returns the same values as the one above this:
 
Sum =
VAR a1 = max ( 'Test table'[Time Variable] )
VAR a2 = max ( 'Test table'[Product variable] )
VAR t1 =
SELECTCOLUMNS(
   all ( 'Test table' )
   , "Column1" , 'Test table'[Time Variable]
   , "Column2" , 'Test table'[Product variable]
   , "Column3" , 'Test table'[Quantity variable]
   )
RETURN
sumx (
   filter ( t1
      , if ( HASONEVALUE( 'Test table'[Time Variable] ) , [Column1] <= a1 , TRUE() )
      && if ( HASONEVALUE( 'Test table'[Product variable] ) , [Column2] = a2 , TRUE() )
      )
   , [Column3]
   )
 
the results for product variable 'B' are missing at time variables 2 and 6, as they are missing in the table. The issue is caused by the lack of implicit context. Adding an 'ALL' is like multiplying by 10... on a value of 0. Removing all filters from no records table reference still returns no records. When there is a record, it will act like 'normal'. 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'?
11 REPLIES 11
Highlighted
Super User IV
Super User IV

Re: YTD with no context

@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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Continued Contributor
Continued Contributor

Re: YTD with no context

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 :). 

Highlighted
Super User IV
Super User IV

Re: YTD with no context

@DouweMeer I'm guessing because apparently: 

Sum =

VAR a1 = max ( 'Test table'[Time Variable] )
RETURN
calculate (
   sumx ( filter ( 'Test table' , 'Test table'[Time Variable] <= a1 ) , [Quantity variable] )
   , all ( 'Test table'[Time Variable] )
   )
 
Is not returning what you want. And the reason is that since there is no 2 I'm guessing that your a1 is getting set to blank for the intersection of B and 2 and thus, you need a disconnected table so that you always have a value for Time Variable and thus a1.
 
That's my opinion.

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Continued Contributor
Continued Contributor

Re: YTD with no context

@Greg_Deckler  Nah, it's not the VAR. 

 

Sum = calculate ( countrows ( 'Test table' ) , ALLSELECTED( ( 'Test table'[Time Variable] ) ) )
 Untitled.png
Sum = calculate ( countrows ( 'Test table' ) , all ( 'Test table' ) )
Untitled2.png
The table reference of 'Test table' in the 'Sum' measure is just not existing. I know I've dealt with this before. Can't remember whether I had a solution... or I just gave up on it.
Highlighted
Super User IV
Super User IV

Re: YTD with no context

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...


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Continued Contributor
Continued Contributor

Re: YTD with no context

@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:

Sum =
CALCULATE(
   SUM ( 'Table'[Quantity variable] ),
   ALL( 'Table'[Time Variable] )
   )
You get the following results:
Untitled.png
And lord behold, at time variables 2 and 6 for product variable B there a no results. 
 
It is like arguiing gravity with you (not a perfect comparison). I drop a feather on earth in air or vacuum. Next I try to do the same experiment in space where there is no gravity but I notice 'nothing happens' when I drop the feather in air. And you are like, yea, it is the feather. Here, if I use vacuum, nothing happens either. Like if you would magically transform the feather in a stone, it would 'fall down'. And I'm like, no, if I use it in vacuum nothing happens with the stone either. And you're like: "nobody can be forced to take advice. See attached PBIX, it really is the feather."
 
As said before, I think the issues lies in the table reference. There is none. Both the VAR  and the RETURN have no rows in their table reference as you can see in your PBIX as in my experiments I mentioned before with the countrows from an earlier message as mentioned above in this comment.  
 
Let me bounce back your remark 'nobody can be forced to take advice'. True, but nobody can be forced either to see the underlining problem rather than the symptoms they prefer to see because they do understand those. Are you willing to see the underlining problem?
Highlighted
Super User IV
Super User IV

Re: YTD with no context

@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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Continued Contributor
Continued Contributor

Re: YTD with no context

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. 

Highlighted
Super User IV
Super User IV

Re: YTD with no context

@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...


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors