Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |