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?
User | Count |
---|---|
23 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |