need help with DAX optimisation

[as1195]

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 creating the virtual table "Manuf"  in the data source helps?

Top Retailer =
VAR Time =
    SELECTEDVALUE ( 'Time'[TimeCalculation] )
VAR Manuf =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( ALLSELECTED ( 'Manufacturer' ), [Manufacturer] ),
            "TotalValue",
                CALCULATE (
                    SUM ( 'Sales'[Value] ),
                    ALL ( 'Time' ),
                    'Time'[Offset] = 0,
                    'Time[TimeCalculation] = TimeCalculation,
                    ALL ( Brand )
                )
        ),
        AND (
            NOT ( [Manufacturer] ) IN { "abc","xyz"},
            NOT ( ISBLANK ( [TotalValue] ) )
        )
    )
VAR Top1 =
    TOPN ( 1, Manuf, [TotalValue], DESC )
RETURN
    MAXX ( Top1, [Manufacturer] )

[FreemanZ]

In a post like this and in most cases, the requirement/expectation and sample data are more important than any line of code.

[amitchandak]

@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]

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?