Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
@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!
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])
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.
@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
- 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.