Dynamic table based on a "matrix" RRS feed

  • Question

  • Hi,


    I’m receiving an Excel file containing details about contracts.

    The business users are using another Excel file, called a “matrix” for calculating a new extra column for each line in the contracts one.


    The “matrix” look like this:


    They are using it as a kind of Case statement.

    For example:

    The “in” clause is really important because at any time the list can change and the users need to be able to refresh to verify different scenarios/hypothesis.

    For now, I dealt it by playing with dynamic lists in M.

    But the “matrix” is quite big which mean a lot of lists. The contracts file is also quite important. As a result I’m facing horrible performances (more than 2 hours to refresh my PowerBI).

    I was wondering if it could not be more efficient to do it in the data model by creating a new table that could be the result of my contracts and matrix mix, with kind of dynamic case.

    But I’m not comfortable enough in Dax to do it.

    Any guess if it’s possible, better performances and how ?

    Thanx for any help or advices.



    Friday, May 19, 2017 1:39 PM