Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I need somehelp in rewriting my DAX Measure. Below Measure is taking atleast 2-3 mins to get the data from the tables.
[Demand],[MonthlySupply] &[NetAvailSupplyDemand] are existing measures in the Cube with some inbuilt logic.
I need to get the count of distinct product ID's based on conditions used in the measure. I have many other dimensions in the cube and below measure should be able to return data based on the selection ( like Area/ Time). How can i better write the below measure and give the same expected output?
For context - product table has 1.6M unique products, Demand table has 215M records and Supply table has 147M records
Product Coverage:=
VAR RowCount =
COUNTROWS (
FILTER (
SUMMARIZE (
'Demand Table',
'Product'[ID]
),
(IF (
(
( ISBLANK ( [Demand] ) = TRUE || [Demand] <= 0 )
&& ( ISBLANK ( [MonthlySupply] ) = TRUE || [MonthlySupply] <= 0 )
&& ( ISBLANK ([NetAvailSupplyDemand] ) = TRUE | [NetAvailSupplyDemand] <= 0 )
) || ( (ISBLANK ( [Demand] ) = TRUE || [Demand] <= 0 ) && [MonthlySupply] > 0),
-1,
IF (( ISBLANK ( [Demand] ) = FALSE || [Demand] > 0) && ( ISBLANK ( [MonthlySupply] ) = TRUE|| [MonthlySupply] = 0),-1,[NetAvailSupplyDemand])
) >= 0
)))
RETURN IF ( ISBLANK ( RowCount ) = TRUE && [NetAvailSupplyDemand] <= 0,0,RowCount )
@gardas_swathi Try this:
Product Coverage :=
VAR DemandProductIDColumn =
VALUES ( 'Demand Table'[Product ID] )
VAR NewColumns =
ADDCOLUMNS (
DemandProductIDColumn,
"@Demand", [Demand],
"@MonthlySupply", [MonthlySupply],
"@NetAvailSupplyDemand", [NetAvailSupplyDemand]
)
VAR FilterRows =
FILTER (
NewColumns,
VAR Demand_True_LessThanZero =
ISBLANK ( [@Demand] ) = TRUE
|| [@Demand] <= 0
VAR MonthlySupply_True_LessThanZero =
ISBLANK ( [@MonthlySupply] ) = TRUE
|| [@MonthlySupply] <= 0
VAR NetAvailSupplyDemand_True_LessThanZero =
ISBLANK ( [@NetAvailSupplyDemand] ) = TRUE
|| [@NetAvailSupplyDemand] <= 0
VAR Demand_FalseNotZero =
ISBLANK ( [Demand] ) = FALSE
|| [Demand] > 0
VAR MonthlySupply_True_Zero =
ISBLANK ( [@MonthlySupply] ) = TRUE
|| [@MonthlySupply] = 0
VAR Result =
IF (
OR (
Demand_True_LessThanZero && MonthlySupply_True_LessThanZero
&& NetAvailSupplyDemand_True_LessThanZero,
Demand_True_LessThanZero
&& [@MonthlySupply] > 0
),
-1,
IF (
Demand_FalseNotZero && MonthlySupply_True_Zero,
-1,
[@NetAvailSupplyDemand]
)
)
RETURN
Result > 0
)
VAR RowCount =
COUNTROWS ( FilterRows )
VAR Result =
IF ( ISBLANK ( RowCount ) = TRUE && [NetAvailSupplyDemand] <= 0, 0, RowCount )
RETURN
Result
Hi @AntrikshSharma ,
I tried to execute VAR New Columns to see the data. I observed that MonthlySupply and NetAvailSupplyDemand are not slicable by productID. Screenshot for reference. I have changed the measure names in my original post to make it simple. Below screenshot the measure names . All these tables are related to Product Table. Monthly Supply is all same for all the product ID's . Net Avail Formula is MonthlySupply-Demand, so Net Avail values seem to change a bit . But overall values in below table are incorrect. I need to get Demand, Monthly Supply and Net Avail by Product ID and then do all the checks
@gardas_swathi Can you share the screenshot of the diagram view, use VALUES ( Product'[ID] ) from product table as I forgot there are multiple facts. So VALUES ( Demand[ProductID] ) won't transfer the filter from context transition to other tables that aren't related to Demand Table.
Hi @gardas_swathi
Please try
Product Coverage :=
VAR RowCount =
COUNTROWS (
FILTER (
SUMMARIZE ( 'Demand Table', 'Product'[ID] ),
VAR Demand = [Demand]
VAR MonthlySupply = [MonthlySupply]
VAR NetAvailSupplyDemand = [NetAvailSupplyDemand]
RETURN
IF (
(
(
ISBLANK ( Demand )
|| Demand <= 0
)
&& (
ISBLANK ( MonthlySupply )
|| MonthlySupply <= 0
)
&& (
ISBLANK ( NetAvailSupplyDemand )
|| NetAvailSupplyDemand <= 0
)
)
|| (
(
ISBLANK ( Demand )
|| Demand <= 0
)
&& MonthlySupply > 0
),
-1,
IF (
(
NOT ISBLANK ( Demand )
|| Demand > 0
)
&& (
ISBLANK ( MonthlySupply )
|| MonthlySupply = 0
),
-1,
NetAvailSupplyDemand
)
) >= 0
)
)
RETURN
IF ( ISBLANK ( RowCount ) && [NetAvailSupplyDemand] <= 0, 0, RowCount )
Hi @tamerj1 ,
Thanks for your response. This solution has not reduced the performance significantly. Its taking 30 seconds less than it used to before. However i am seeing this error in the last line. Is it because we are using NetAvailSupplyDemand Variable outside the context of creation?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |