Forum Discussion

as1195's avatar
as1195
Icon for Helper I rankHelper I
3 years ago

need help with DAX optimisation

Hi, I am trying to find top competitor using below measure.Whenever I am using this measure in the report this is hitting performance very badly.how should I optimise the  below measure?Will creat...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

as1195 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

  • as1195's avatar
    as1195
    Icon for Helper I rankHelper I
    3 years ago

    Hi Amit,

    Unfortunaletly I am not able to attach sample file here.Please find the snippet for the same.
    Manufacturer table holds unique manufacturer.


    Manufacture pivot table has Top Competitor defined for each of the manufacturer along with inidvidual manufacturer.


    Sales table holds sales and other details.


    Time table holds Latest12Weeks and Latest4week data.


    What I am trying to find is the Top Retailer .For ex
    For Egypt & Vegetables, when Top Competitor is selected from slicer the query should hit all the Egypt and Vegetables combination from sales tables and give the highest sales manufacturer as a result.

     

    When user selects any other manufacturer other than Top Competitor,the sales values should show against the selected manufacturer


    Top Retailer Measure=

    Top Retailer =
    var timevalue=SELECTEDVALUE('Time'[Time])
    VAR Manuf1 =
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE ( ALLSELECTED ( 'Manufacturer' ), [Manufacturer] ),
                "TotalValue",
                    CALCULATE (
                        SUM ( 'Sales'[Sales Value] ),
                        all('Time'),
                        'Time'[Time]=TIMEVALUE
                    )
            ),
            AND (
                NOT ( [Manufacturer] ) IN {"Others" },
                NOT ( ISBLANK ( [TotalValue] ) )
            )
        )
    VAR Top1 =
        TOPN ( 1, Manuf1, [TotalValue], DESC )
    RETURN
        MAXX ( Top1, [Manufacturer] )

    Is there any way I can achieve the same result without creating a table inside the measure?