Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Screenshot of virtual table:
Solved! Go to 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...
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
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...
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
User | Count |
---|---|
96 | |
85 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |