(DAX & Power BI ) How to get data when using filters on fact table while showing dimension columns?

Question

One of the measures I've created using DAX functions, 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 of instances from 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 (in Power BI Desktop) 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 and shows measure calculation results for each date.

When I just trying to show this measure with no other rows/columns, it also shows nothing.

I've double checked it, and found that this measure gives correct value (last date from Fact table):

CALCULATE(
LASTDATE('Dim Dates'[Dim Date]),
CROSSFILTER('Fact Data'[fact date id], 'Dim Dates'[dim date id], BOTH)
)

I've also tried this measure formula:

Fact Sum of Value = CALCULATE(SUM(Value),
FILTER('Fact Data', LASTDATE('Dim Dates'[Dim Date])),
)

But this formula ignores filter on last date and just summarize values from all dates, when no date specified. However, it shows proper value when I create table/matrix with "Fact date id" as columns, which is better than nothing.

Alexander Skakun · Accepted Answer · 2024-02-26 18:53:31Z

0

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!

answered Feb 26, 2024 at 18:53

Alexander Skakun

156 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

(DAX & Power BI ) How to get data when using filters on fact table while showing dimension columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related