How to get the value from the same table but with different context ? ...

[Ricardo77]

Hello, i have an architectural doubt on DAX, both: how I should I do to get the value, while not forgetting performance and BPs... 

Basically i pick 2 codemoments from a matrix, and i have to calculate some kind of variation like

(somevalue from factual in codemoment_t_1 / somevalue2 from dimension in codemoment_t_1) *
(somevalue2 from dimension in codemoment_t - somevalue2 from dimension in codemoment_t_1)

My only problem right now is the one highlighted in red: i'm trying to get the value but i don't know how to do it.

Conceptually, should be something like ...

(CALCULATE(RELATED(DIMENSION[SOMEVALUE2]); FACTUAL[CODEMOMENT] = CODEMOMENT_T)

... or like i'm doing there, using the ID and making the relation temporarily n to 1 ...

RESULT  = 
VAR SELECTED_2_MOMENTS = ALLSELECTED(FACTUAL[CODEMOMENT])
VAR CODEMOMENT_T =   CALCULATE(MAX(FACTUAL[CODEMOMENT]); FACTUAL[CODEMOMENT] IN SELECTED_2_MOMENTS) 
VAR CODEMOMENT_T_1 = CALCULATE(MIN(FACTUAL[CODEMOMENT]); FACTUAL[CODEMOMENT] IN SELECTED_2_MOMENTS)

RETURN IF (CODEMOMENT_T <> CODEMOMENT_T_1; 
            CALCULATE (
                SUMX(
                    FACTUAL;        
                    DIVIDE(FACTUAL[SOMEVALUE]; RELATED(DIMENSION[SOMEVALUE2])) 
                    *
                     -- calculation problem next line 					
                    (CALCULATE(MAX(DIMENSION[SOMEVALUE2]); FACTUAL[CODEMOMENT] = CODEMOMENT_T; CROSSFILTER(FACTUAL[ID_CONCATENATED_ENTRY]; DIMENSION[ID_CONCATENATED_ENTRY]; OneWay))
					 -- calculation problem previous line
                                - RELATED(DIMENSION[SOMEVALUE2]))  
                    ;
                    FACTUAL[CODEMOMENT] = CODEMOMENT_T_1)
           )            

This is the model associated:

Can someone help me out? Thanks in advance.

[Greg_Deckler]

@Ricardo77 -

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Ricardo77]

Hello @Greg_Deckler ,

Thanks, let me update the doubt:
So the measure is this and my problem is getting to a different context in that line between comments

RESULT  = 
VAR SELECTED_2_MOMENTS = ALLSELECTED(FACTUAL[CODEMOMENT]) -- this will pick 11200 and 13500
VAR CODEMOMENT_T =   CALCULATE(MAX(FACTUAL[CODEMOMENT]); FACTUAL[CODEMOMENT] IN SELECTED_2_MOMENTS) -- so, 13500
VAR CODEMOMENT_T_1 = CALCULATE(MIN(FACTUAL[CODEMOMENT]); FACTUAL[CODEMOMENT] IN SELECTED_2_MOMENTS) -- 11200

RETURN IF (CODEMOMENT_T <> CODEMOMENT_T_1; 
            CALCULATE (
                SUMX(
                    FACTUAL;        
                    DIVIDE(FACTUAL[SOMEVALUE]; RELATED(DIMENSION[SOMEVALUE2])) 
                    *
                     -- calculation problem next line, need the value from 13500 					
                    (CALCULATE(MAX(DIMENSION[SOMEVALUE2]); FACTUAL[CODEMOMENT] = CODEMOMENT_T; CROSSFILTER(FACTUAL[ID_CONCATENATED_ENTRY]; DIMENSION[ID_CONCATENATED_ENTRY]; OneWay))
					 -- calculation problem previous line
                                - RELATED(DIMENSION[SOMEVALUE2]))  
                    ;
                    FACTUAL[CODEMOMENT] = CODEMOMENT_T_1)-- so, all SUMX is running over 11200, EXCEPT that value from 13500
           )       

Conceptually, should be something like this, where codemoment_t = 13500; that's why i tried to play with crossfilter direction and RELATED to pick the value from the dimension on a different context. The idea is, for the same context, just change CODEMOMENT from 11200 for 13500

(CALCULATE(RELATED(DIMENSION[SOMEVALUE2]); FACTUAL[CODEMOMENT] = CODEMOMENT_T)

Factual

ID_CONCATENATED_ENTRY	CODEMOMENT	TYPEOFENTRIE	SOMEVALUE
ABC11200	11200	S	806663
ABC11200	11200	S	264560.44
ABC11200	11200	S	67574694.67
ABC11200	11200	S	1148038
ABC11200	11200	S	364358905

Dimension

ID_CONCATENATED_ENTRY	CODEMOMENT	SOMEVALUE2
ABC11200	11200	0.00003751
ABC11200	11200	0.00324386
ABC11200	11200	0.007120278
ABC11200	11200	0.00838303
ABC11200	11200	0.00949994
ABC13500	13500	0.00003989
ABC13500	13500	0.00324
ABC13500	13500	0.0071
ABC13500	13500	0.008
ABC13500	13500	0.008

Dax calculation should do, for each line ….

( 806663 / 0.00003751) * (0.00003989 - 0.00003751)

The final result should be the sum of the last column of this table:

SOMEVALUE  in 12200 | SOMEVALUE2 in 12200 | SOMEVALUE in 13500 | SOMEVALUE is again from 12200

Thanks in advance for your help!

[Greg_Deckler]

@Ricardo77 Wondering if maybe you need to use TREATAS but I can't really tell because I can't mock this up without data.