DAX query question / performance issue

[Anonymous]

Hey guys,

i've just started working with DAX and need some help with a DAX query / performance issue. 

I'm using direct query mode and display the data in a matrix.

There are two Tables (one fact (MV_AGG_FACT_PRODUCT_CAL_YEAR), one dimension table(MD_EXCHANGE_RATE_FORECAST)) in a many to many relationship.

In the fact table, there are revenue values and in the dimension table, there are corrospending exchange rate values. I want to multiply the revenue in the fact table with the exchange rate. The two tables are connected via a key column (SN_CURRENCYFORECAST_MERGED). One revenue can have multiple exchange rates assigned to.

I've created a measure (REV1) what is doing a calculation on the fact table. The result of this calculation should be multiplied with corresponding exchange rate. This calculation works pretty fast.

I've then created a second measure (because i just didnt know how die implement these calculation in one DAX query) what is "joining" the exchange value from the dimension table.

Measure = SUMX(MV_AGG_FACT_PRODUCT_CAL_YEAR,CALCULATE ( FIRSTNONBLANK ( MD_EXCHANGE_RATE_FORECAST[RATE], 1 ), FILTER ( MD_EXCHANGE_RATE_FORECAST, MD_EXCHANGE_RATE_FORECAST[SN_CURRENCYFORECAST_MERGED] = [SN_CURRENCYFORECAST_MERGED] ) ) * [REV1])

It works, i'm getting the correct values. But the query performance is really really slow now.

Any ideas?

[Anonymous]

@lbendlin you are completely right.

i was that mutch focussed on DAX that i didnt see the basic problem. Of course, exchange rates is no dimension. it is a fact. I've adjusted the datamodel (merged those two fact tables to one) and got rid of the many to many relationship. 

@wdx223_Daniel thanks also for that! 

[wdx223_Daniel]

Measure = SUMX(MV_AGG_FACT_PRODUCT_CAL_YEAR,VAR _filter=FILTER ( VALUES(MD_EXCHANGE_RATE_FORECAST[SN_CURRENCYFORECAST_MERGED]), MD_EXCHANGE_RATE_FORECAST[SN_CURRENCYFORECAST_MERGED] = [SN_CURRENCYFORECAST_MERGED] ) RETURN CALCULATE ( MIN ( MD_EXCHANGE_RATE_FORECAST[RATE] ), _filter ) * [REV1])

[lbendlin]

You do not want to have bidirectional search in a M:M relationship.  You don't even want a M:M relationship between a dimension table and a fact table - this basically says that your dimension table is fake, it is a fact table pretending to be a dimension.  Please rethink your data model.  Ideally a dimension points to a fact in 1:M with single search direction.

[Anonymous]

@lbendlin  thanks for your reply.

There is one Fact Table, one Dimension Table. Connected with a many to many relationship via a key.

Of course, there are some more Dimensions, but they are not relevant for this report.

With the query builder in DAX Studio i was able to generate the following query.

MyMessure1 just contains a simple calculation of some columns on the fact table. This works as expected.

The result is correct, so far.

What i now whant to do is: multiply RATE with MyMeasure. In a final step i want to get the SUM of those results by customer_code. In this case i would expect the following result:

As i already wrote in the topic, i found a solution for doing that. But this is really really slow.

Kind regards

Jack

[lbendlin]

- show your data model

- install DAX Studio and use it to profile your queries and figure out how to optimize the query performance. Watch the videos that describe how to do that.