cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

5 REPLIES 5
Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@Marek_Wojciech try this:

``````Gen 1 Measure =

SUMX(
FILTER (
'tblGen',

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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.

Super User

@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]
)``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors