Forum Discussion

Marek_Wojciech's avatar
Marek_Wojciech
Frequent Visitor
2 years ago

Problem with correctly filtering a fact table based on another table

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

 

 

 

 

Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Marek_Wojciech try this:

     

    Gen 1 Measure = 

SUMX(
    FILTER ( 
       'tblGen',

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

     

     

     

    • Marek_Wojciech's avatar
      Marek_Wojciech
      Frequent Visitor
      2 years ago

      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's avatar
    parry2k
    Super User
    2 years ago

    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's avatar
      Marek_Wojciech
      Frequent Visitor
      2 years ago

      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 >= ,<=).

       

       

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

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