Forum Discussion

leolapa_br's avatar
leolapa_br
Resolver II
3 years ago
Solved

Get values from a temporary table variable

Below is a piece of the fact table 'fTrans' that provides context to my question.   Ticker Date Sale date related to respective purchase Days Transaction Shares Price Purchase total A...
"DAX"
Lookup value
  • leolapa_br's avatar
    leolapa_br
    3 years ago

    lbendlin your approach sure provided the stepping stones for helping me get across the finish line.

     

    Thanks very much for your valuable input!

     

    Just a couple of tweaks to your code were needed to reach the goal:

    • [Days] and [Purchase total] are measures instead of columns of table 'fTrans', so all it was needed was to remove 'fTrans' from both within SUMX.
    • Your calculation provides the equivalent in Excel of a SUMPRODUCT between [Days] and [Purchase total], so all it was needed in order to get a proper weighted average of [Days] was to divide that by the SUM of [Purchase total], and I did that by wrapping it around CALCULATE with the same filters (at least to me it somewhat looked odd a CALCULATE within another CALCULATE, but that's the only way I know how to do it for that particular case - perhaps creating a VAR for that would be more within the best practice guidelines).

    So the final code looks as follows:

     

    Table = 
	ADDCOLUMNS( 
		SUMMARIZE( 
			FILTER( 
				fTrans, 
				fTrans[Transaction] = "Sale" 
			), 
			fTrans[Ticker],
			fTrans[Date] 
		), 
		"WavgDays", 
		VAR Ticker_Ref = [Ticker]
		VAR Date_Ref = [Date]
		RETURN
			CALCULATE( 
				SUMX( fTrans,
					DIVIDE( 
						[Days] * [Purchase total], 
						CALCULATE( 
								[Purchase total], 
							ALL( fTrans ), 
							fTrans[Ticker] = Ticker_Ref, 
							fTrans[Sale date related to respective purchase] = Date_Ref 
						)
					)
				), 
				ALL( fTrans ), 
				fTrans[Ticker] = Ticker_Ref, 
				fTrans[Sale date related to respective purchase] = Date_Ref 
			) 
		)

     

    Thanks!

lbendlin's avatar
lbendlin
Super User
3 years ago

You will need to indicate what these measures do. You cannot convert a calculated column formula into a measure formula like that - you need to think deeply about the very different concepts, and most of the time you need to write a completely new measure from scratch. 

leolapa_br's avatar
leolapa_br
Resolver II
3 years ago

lbendlinI thought that once I came up with the calculated column I wanted to get, namely [WavgDays], I could create a measure using CALCULATE to iterate row by row through the table visual and pick up the value from [WavgDays] on the calculated table 'fWavgDays' to each "Sale" row on the table visual whose respective [Ticker] and [Date] match those on 'fWavgDays'.

 

Such measure would be something similar to a RELATED type process. Or talking in Excel terms, an INDEX/MATCH type deal, where the visual table is the destination and the source table on this case is 'fWavgDays'.

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    You cannot create a calculated column from measures (as you seem to be trying to do).  You may need to start over (as I mentioned above).

     

    Please provide sample data that fully covers your issue. 

    • leolapa_br's avatar
      leolapa_br
      Resolver II
      3 years ago

      lbendlin I believe I didn't make myself clear and I apologize for that...

       

      The table from the above reply that originated this follow-up question is not a calculated table, but just some sample rows from a table visual.

       

      Below is a screenshot of the visual, which is basically a mirror of the table previously shown (I hadn't originally provided a screenshot because often times when I do screenshots on Stack Overflow I get frowned upon, so I guess I got bitten by the web shame snake...).

       

       

      With that said, both columns in red "Days average 1" and "Days average 2" are not calculated columns on a calculated table, but measures dropped on a table visual whose codes are the ones previously provided.

       

      So as I mentioned on my last note, once reaching my first goal of figuring out a way to calculate weighted average days for sales transactions, which you helped me do it by getting to [WavgDays], then all I needed to do after that was to come up with a measure that retrieves values from that calculated column [WavgDays], and that's what I'm trying to do now.

       

      I thought CALCULATE could do that job since:

      1. Together with a row iterator funcion should iterate row by row on whatever table we provide (on this case 'fWavgDays').
      2. It therefore allows to relate to the calculated column [WavgDays] on its first expression argument.
      3. Narrows down to the matching [Ticker] and [Date] on its second filter argument.

      But I'm either doing something wrong with CALCULATE that's instead providing the overall total of days for every row, or CALCULATE shouldn't be used at all and instead I should take another route. Either way, I was hoping you could help me point to the right direction here...

       

      Thanks for your patience and willingness to help!

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago

        After you create the calculated table it will not automatically be included in the data model. It is a "disconnected" table.  That is the reason why you see the same number all over the column/measure.  Wire the table into your data model as needed.