Forum Discussion
SUMX/PRODUCTX Not Returning Expected Result
Hi,
I have a measure where I am trying to calculate a running product of the variable _kmintermediate at each number from 1 to 120 which are saved in 'Dim%Series'[Series] to plot on a graph.
If I add any of the intermediate variable (_countdeadpast, _countalivepast, _countdead, _kmintermediate) onto the graph it works perfectly with expected results.
However, when I apply PRODUCTX to _kmintermediate (or any of the other variables) it returns a bunch of incorrect results and does not start at 0 on the graph. The results will start at 26 which is the lowest value of FactPMBL[%DueCalc] in that table. I'm scratching my head why the filter context seems to change when putting the variable through a PRODUCTX function.
The reason that this all needs to be calculated in a measure is becasue there are many slicers I need to apply to this data.
I would really appreciate any insight as to why the PRODUCTX is changing things!
KM =
var _percentcutoff = SELECTEDVALUE('Dim%Series'[Series])
var _description = SELECTEDVALUE(DimServType[Description])
var _fleet = SELECTEDVALUE(DimFleet[Fleet])
var _supplier = SELECTEDVALUE(DimSupplier[SupplierNameShort])
var _countdeadpast = COUNTX(FILTER(ALLSELECTED(FactPMBL), FactPMBL[SupplierName] = _supplier && FactPMBL[Fleet] = _fleet && FactPMBL[%DueCalc] > _percentcutoff && FactPMBL[InstalledByNACG] = "IN" && FactPMBL[CurrentlyInstalledComponent] = BLANK()),FactPMBL[UnitAndLastCompleted]) -- working
var _countalivepast = COUNTX(FILTER(ALLSELECTED(FactPMBL), FactPMBL[SupplierName] = _supplier && FactPMBL[Fleet] = _fleet && FactPMBL[%DueCalc] >= _percentcutoff && FactPMBL[InstalledByNACG] = "IN" && FactPMBL[CurrentlyInstalledComponent] = "Yes"),FactPMBL[UnitAndLastCompleted]) -- working
var _countdead = COUNTX(FILTER(ALLSELECTED(FactPMBL), FactPMBL[SupplierName] = _supplier && FactPMBL[Fleet] = _fleet && FactPMBL[%DueCalc] = _percentcutoff && FactPMBL[InstalledByNACG] = "IN" && FactPMBL[CurrentlyInstalledComponent] = BLANK()),FactPMBL[UnitAndLastCompleted]) -- working
var _numatrisk = _countalivepast + _countdeadpast
var _kmintermediate = if([zzzz_numatrisk]=BLANK(),0, 1-([zzzz_countdead]/[zzzz_numatrisk]))
VAR _series = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Dim%Series'),
'Dim%Series'[Series] <= SELECTEDVALUE('Dim%Series'[Series])),
"COLUMN", 'Dim%Series'[Series]))
return
PRODUCTX(_series,_kmintermediate)
Hi WNAGGE ,
According to your description, there are multiple tables in the dax expression.
1.you can check if there is a correct relationship between the tables, for more information about the relationship between the tables, you can refer to the documentation: Model relationships in Power BI Desktop - Power BI | Microsoft Learn.
2.on the other hand, the variable `_ kmintermediate` depends on the other metrics `[zzzz_numatrisk]` and `[zzzz_countdead]`. Ensure that these measures are calculated correctly in the current filtering context and do not return unexpected values.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. we can better understand the problem and help you.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi WNAGGE ,
According to your description, there are multiple tables in the dax expression.
1.you can check if there is a correct relationship between the tables, for more information about the relationship between the tables, you can refer to the documentation: Model relationships in Power BI Desktop - Power BI | Microsoft Learn.
2.on the other hand, the variable `_ kmintermediate` depends on the other metrics `[zzzz_numatrisk]` and `[zzzz_countdead]`. Ensure that these measures are calculated correctly in the current filtering context and do not return unexpected values.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. we can better understand the problem and help you.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for your help! I removed the relationship between 'Dim%Series'[Series] and FactPMBL[%DueCalc] which solved all my problems thankfully. I'd spent way too much time staring at this so I'm happy it was something simple.
