DAX formula percentage - Better way to write it?

[TimvMechelen]

Hi All,

I wrote a measure to calculate the percentage of available Material numbers (in terms of stock).
The things I must include in the formula:

• Filter Material numbers based on: MaterialMaster[Plant] = "NL05"

• Filter Material numbers based on: MaterialMaster[Deletion Flag at PlantLvl] = False

• Filter Material numbers based on: MaterialMaster[CODP] = "BTS (Buy to Stock)" || MaterialMaster[CODP] = "MTS (Make to Stock)"

• Filter Stock based on: TotalStock[Plant] = "NL05" && TotalStock[StockType] = "Normale voorraad"

I created a virtual table with all the materials I'd like to take into account and added a column with a "1" if there is stock available and with a "0" if there isn't stock available.

It works, but I think it can be a lot faster and I'd like to learn more about DAX. Would appreciate it if somebody could help me.

The code:

Availability Rate =
// Create table with material numbers and stock with the filter criteria.

VAR __Table =

ADDCOLUMNS(    

    Summarize(

        FILTER(

            FILTER(MaterialMaster, MaterialMaster[Plant] = "NL05" && MaterialMaster[Deletion Flag at PlantLvl] = False)

            , MaterialMaster[CODP] = "BTS (Buy to Stock)" || MaterialMaster[CODP] = "MTS (Make to Stock)"  ),

    MaterialMaster[Material],

    "TotalStock_X", Calculate ( SUM( TotalStock[Stock Quantity] ) , FILTER( TotalStock, TotalStock[Plant] = "NL05" && TotalStock[StockType] = "Normale voorraad"  ) )

    ),

"Available", IF([TotalStock_X] > 0, 1, 0)

)

// Add column with total available and total material numbers.

VAR __TableExtend =

ADDCOLUMNS(

__Table,

    "TotalMaterials",

    CALCULATE(

        SUMX( __Table, [Available] ),

        FILTER( __Table, [Available] >= 0 )

    ),

    "TotalAvailable",

    Calculate(

        COUNTX( __Table, [Available] ),

        Filter( __Table, [Available] > 0 )

    )

)

// Add column to calculate the percentage.

VAR __TableAvailabilityRate =

ADDCOLUMNS(

    __TableExtend,

    "AvailabilityRateCalc.",

    DIVIDE([TotalMaterials],[TotalAvailable])

)

// Get one value, since they're all the same.

RETURN

MAXX(__TableAvailabilityRate,[AvailabilityRateCalc.])

Screenshot of virtual table:

[v-yiruan-msft · Accepted solution]

Hi @TimvMechelen ,

Please update the formula of your measure as below and check if it can work faster...

Availability Rate =
// Create table with material numbers and stock with the filter criteria.
VAR __Table =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                MaterialMaster,
                MaterialMaster[Plant] = "NL05"
                    && MaterialMaster[Deletion Flag at PlantLvl] = FALSE
                    && ( MaterialMaster[CODP] = "BTS (Buy to Stock)"
                    || MaterialMaster[CODP] = "MTS (Make to Stock)" )
            ),
            MaterialMaster[Material],
            "TotalStock_X",
                CALCULATE (
                    SUM ( TotalStock[Stock Quantity] ),
                    FILTER (
                        TotalStock,
                        TotalStock[Plant] = "NL05"
                            && TotalStock[StockType] = "Normale voorraad"
                    )
                )
        ),
        "Available", IF ( [TotalStock_X] > 0, 1, 0 )
    ) // Add column with total available and total material numbers.
VAR __TableExtend =
    ADDCOLUMNS (
        __Table,
        "TotalMaterials",
            CALCULATE (
                SUMX ( __Table, [Available] ),
                FILTER ( __Table, [Available] >= 0 )
            ),
        "TotalAvailable",
            CALCULATE (
                COUNTX ( __Table, [Available] ),
                FILTER ( __Table, [Available] = 1 )
            )
    ) // Add column to calculate the percentage.
VAR __TableAvailabilityRate =
    ADDCOLUMNS (
        __TableExtend,
        "AvailabilityRateCalc.", DIVIDE ( [TotalMaterials], [TotalAvailable] )
    ) // Get one value, since they're all the same.
RETURN
    MAXX ( __TableAvailabilityRate, [AvailabilityRateCalc.] )

In addition, you can refer the following links to optimize your DAX...

DAX Best Practice Guide

Optimizing DAX Video Course

Use Performance Analyzer to examine report element performance (can see and record logs that measure how each of your report elements performs when users interact with them, and which aspects of their performance are most (or least) resource intensive.)

If the above one can't help you get the desired result, please provide some sample data in your table 'MaterialMaster' and 'TotalStock' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards