How do I use DAX to filter a fact table where a value in one dimension table is greater than a value in a different dimension table

0
votes

I have a fact table with two linked dimension tables. I am trying to write a DAX filter that filters the fact table based on whether a date in one dimension table is greater than

Something along the lines of this (although obviously this doesn't work).

CALCULATE ( SUM ( factFinancial[Value] ), dimOne[Date1] > dimTwo[Date2] )

Any help would be greatly appreciated

2
powerbidax

2 Answers

0
votes

It's hard to answer without a specific example to work with but I suspect you can find a solution along these lines:

CALCULATE (
    SUM ( factFinancial[Value] ),
    FILTER ( factFinancial, RELATED ( dimOne[Date1] ) > RELATED ( dimTwo[Date2] ) )
)
0
votes

The calculate function can not evaluate your expression, The reasons are:

  1. During its evaluation algorithm, CALCULATE cannot determine whether the condition should replace any existing filter on dimOne [Date1], on dimTwo [Date2], or on none of them.

  2. Row context does not interact automatically with relationships, means that the expression dimOne[Date1] > dimTwo[Date2] is evaluated in row context and the two columns [Date1] and [Date2] are from two different tables, there since we cannot access the corresponding date from [Date2] to each date on [Date1] automatically. In this situation you must use RELATED and RELATEDTABLE DAX functions.

In order to achieve your aim you must use filter and RELATED functions.

Use this code instead to create the measure in factFinancial table :

CALCULATE(SUM (factFinancial[Value]), 
FILTER(factFinancial,RELATED(dimOne[Date1]) > RELATED(dimTwo[Date2])))

Visit : https://www.sqlbi.com/blog/marco/2010/01/03/how-calculate-works-in-dax/

Visit : https://www.microsoftpressstore.com/articles/article.aspx?p=2449191&seqNum=5