Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX query question / performance issue

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?

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    5 years ago
    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's avatar
    lbendlin
    Super User
    5 years ago

    - 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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!