Problem with correctly filtering a fact table based on another table

[Marek_Wojciech]

Hi, I'm taking my first steps in DAX and I have a simple model consisting of two tables. I created two measures (Generation and Generation1), the first returns the sum of generations, the second also returns the sum, but limited by the ComDat dates from the bottom and the DeComDate date from the top for each unit. The results of the latter seem correct at the unit level, but the summary in the sums line is incorrect and returns the total generation. The solution is probably trivial for specialists, but please note that the actual tblGeneration table in my real model has 10 million records. Please indicate the correct definition of the Generation1 measure. Thank you in advance for your help.

Regards Marek_Wojciech,

tblUnits

Unit	ComDate	DeComDate
A	01.01.1900	03.01.2023
B	02.01.2023	01.01.2100
C	01.01.1900	01.01.2100
D	02.01.2023	04.01.2023​

tblGeneration

Date	Unit	power
01.01.2023	A	15
02.01.2023	A	17
03.01.2023	A	13
04.01.2023	A	12
01.01.2023	B	34
02.01.2023	B	24
03.01.2023	B	18
04.01.2023	B	0
01.01.2023	C	24
02.01.2023	C	13
03.01.2023	C	13
04.01.2023	C	20
01.01.2023	D	24
02.01.2023	D	13
03.01.2023	D	13
04.01.2023	D	20

DEFINE
    MEASURE 'tblGeneration'[DateComMeasure] = MIN('tblUnits'[ComDate])
    MEASURE 'tblGeneration'[DateDeComMeasure] = MAX('tblUnits'[DeComDate])
    MEASURE 'tblGeneration'[Generation] = SUMX('tblGeneration','tblGeneration'[power])
    MEASURE 'tblGeneration'[Generation1] = 
	VAR DCM=[DateComMeasure]
	VAR DDCM=[DateDeComMeasure]
RETURN
CALCULATE([Generation],AND('tblGeneration'[Date]<DDCM,'tblGeneration'[Date]>DCM))

[parry2k]

@Marek_Wojciech well you have a solution that's what matters, how we get there, maybe not that important. 🙂 Cheers!!

[parry2k]

@Marek_Wojciech try this:

Gen 1 Measure = 

SUMX(
    FILTER ( 
       'tblGen',

       'tblGen'[Date] < RELATED ( tblUnits[DeComDate] ) &&
       'tblGen'[Date] > RELATED ( tblUnits[ComDate] )
    ),
    'tblGen'[power]
)

[Marek_Wojciech]

At the same time, after careful analysis, I came to the same solution. Thank you for your time and suggestion, which allowed me to find a solution.

[parry2k]

@Marek_Wojciech try this:

Gen 1 Measure = 
VAR __ComDate = MIN('tblUnits'[ComDate])
VAR __DeComDate =  MIN('tblUnits'[DeComDate])
RETURN
SUMX(
    FILTER ( 
       'tblGeneration',
       'tblGeneration'[Date] <= __DecComDate &&
       'tblGeneration'[Date] >= __ComDate
    ),
    'tblGeneration'[power]
)

[Marek_Wojciech]

Thank you for your answer, although the generation for individual units is correct, the summary is still wrong. It should be 133, not 164. This is the sum of the generation for each unit in the range between the ComDate and DeComDate dates of each unit (without generation on these days - in the measure >,< and not >= ,<=).