How to get filtered data when using crossfilter

[AleksTankist111]

Hello. One of the measures I've created using crossfilter DAX function applied for the dates table, shows nothing in certain conditions, and I cannot understand why.

I have two tables coming from OLAP Cubes, let's say "Dim Dates" and "Fact Data". The first one contains dates in different forms (year, month, day, etc.) up to 2030. The Fact table contains some columns with IDs for other tables and column Value.

In the fact table, I have a field called "fact date id", and the same one in Dim Dates ("dim date id"). They are connected in OLAP Cube as 1 to many.

I've created a measure "Fact Sum of Value":

Fact Sum of Value = CALCULATE(SUM(Value),
LASTDATE('Dim Dates'[Dim Date]),
CROSSFILTER('Fact Data'[fact date id], 'Dim Dates'[dim date id], BOTH)

)

The problem comes when I try to put this measure in visual without Row "Dim Date". So, when I want to make a matrix visual with, say, [fact date id] as Row and Fact Sum of Value as "Value", it shows nothing for all [fact date id]. When I add Dim Date ID or Dim Date, it works fine.

When I just trying to show this measure, it also shows nothing.

Could you please support me with this?

[AleksTankist111 · Accepted solution]

I found the solution:

As you could know, it is impossible to use MAX(...) in FILTER in PowerBI Desktop, so you can't just write a filter where:

'Fact Data'[fact date id] = MAX('Fact Data'[fact date id])

But you can store this MAX value in a variable, and then use this variable. Therefore, the solution is following:

Fact Sum of Value = 
VAR max_date_id = MAX('Fact Data'[fact date id])
RETURN CALCULATE(
SUM(Value),
FILTER('Fact Data', 'Fact Data'[fact date id] = max_date_id)
)

That's it!