The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
I'm having some difficulty understanding how I need to adapt the DAX measure to make the monthly subtotal equal to the visual sum of matrix rows. Essentially, I want to calculate the date difference between the date a product was installed (in a particular month) vs. the end of month date. I can't quite get my head around what filter context considerations are needed to make the monthly subtotal add up.
In terms of the granularity, this is needed at the machine level - hence, using the machine serial number from a Dim table containing a complete list of all products/machines vs. the respective machine serial number. However, the measure also needs to work on a date level - where the monthly subtotal should be the aggregated sum of machines time-in-service for the month of installation.
Each row in the transaction table is unique - corresponding to the unique machine serial number - which is the reason the Min function is used to extract the machine install date.
As can be seen from the measure, there are 3x tables being referenced:
Here's the measure written thus far:
I'm not able to upload the PBI file as this consists of business confidental data and therefore, I'm very much hoping that a solution can be proposed based on the description of the issue alone.
Any suggestions on solution and an explanation as to what I'm missing would be hugely appreciated.
Please let me know if additional information/explanation is needed to support a solution.
Solved! Go to Solution.
Aftrer writing an update, it occurred to me that the issue I'm seeing (given the apparent use of the same row value in the date diff aggregation operation) that the issue was more about eval context and the probable incorrect use of variables in the measure. I changed the measure to the following (ensuring the installation date reference/value was now directly used in the measure), which has resolved the issue and the measure now works at the month/year date levek as well as the machine level.
Aftrer writing an update, it occurred to me that the issue I'm seeing (given the apparent use of the same row value in the date diff aggregation operation) that the issue was more about eval context and the probable incorrect use of variables in the measure. I changed the measure to the following (ensuring the installation date reference/value was now directly used in the measure), which has resolved the issue and the measure now works at the month/year date levek as well as the machine level.
Hi Polly,
Apologies for the tardy response! It's been a somewhat hectic week and this is the first opportunity to respond back.
Thank you for the suggestion. I cannot get this to work though and receive the same monthly total as before. I had attempted to use Summarize as a means to group and associate the aggregation measure prior to your suggestion and couldn't manage to make the measure work at the date level.
I guess the filter context from the matrix table at the month/year level results in the situation where the aggregation is not a sum of calculated values (the date diff) associated at the row/machine ID level, but uses a value (which looks to be the same value) corresponding to each of the rows active/in context for the given month. For example, the countrows values for Jan 2017 = 75 (75x machines having been installed during the month of Jan). The current meaure shows an aggregated value of 2175 for Jan 2017, which seems to suggest that the same whole number (a value of 29 in this case) is being used 29x75 = 2175. For other month/year totals, devision of the month/year total by the machine/row count results in a whole number value. Again, this suggests that the same value is being used for each row, (albeit that this value is different for the different months in the year). Again, if I take a specific month reference (March 2017 in this case) which shows a month/year total of 4320 and 144x installed machines/rows, then the row aggregator value would appear to be 4320/144 = 30. So even when using the summarized table to associate machine level date delta values at either the machine or date level the result is the same. Well, I'm sufficiently confused...
Hope the explanation makes sense.
Hi @Grasshopper ,
It seems the measure total value is error.
Please create another measure.
_contract_ = var _a = [POPULATION_OPERATIONAL_TIME_(MONTH)]
var _b = SUMMARIZE('table','table'[date],"aaa",[POPULATION_OPERATIONAL_TIME_(MONTH)])
return
IF(HASONEVALUE('table'[date]),_a,SUMX(_b,[aaa]))
Or you can use the column name in the red circle in the following image instead of the date column.
How to Get Your Question Answered Quickly
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
33 | |
20 | |
16 | |
15 |
User | Count |
---|---|
56 | |
50 | |
36 | |
35 | |
31 |