Forum Discussion
sum or count a measure
Thanks for that.
It looks like the problem is the VAR/RETURN syntax.
If you use VAR to store the result of your existing measure like this, it actually just evaluates once and locks in a single value, evaluated in the overall filter context (i.e. not per week).
We need the validobservation measure to be re-evaluated for every iteration of the SUMX, so the SUMX should refer to a measure (or equivalent expression wrapped in CALCULATE), not a variable.
I would say the cleanest way to do this is to keep your first measure as you have defined it (since you know it's working at the Week level), and call it say validobservation.
Then your new measure should be:
Valid Week Count = SUMX ( VALUES ( TimeTable[Week] ), [validobservation] )That is, the second argument of the SUMX should be a measure, not a variable.
Does that help?
Regards,
Owen
Super UserThanks for the update liran
Just to confirm, could you please post the code for both your original measure and the new measure you created with SUMX?
Also could you post the structure of each table (Query and TimeTable) and the relationship between the two - just to check what I missed.
Regards,
Owen
they related in a on to many where the TimeTable has uniqe values of weeks and the query has weeks for each store and item.
the first measure is the original that i'm using and the seceond is the measure with the sumx.
i'll try to explain more about the measure:
i check for each item and store how many weeks are valid, and a valid means that the item was sold in any store of the database
and that the store sold any other item at this week. means it's valid and i want to count those weeks for each combination of
item and store.
the problem is that when there is no sell for an item in a store at a certain week i don't have a row for this in the data
wich makes it much harder to count the number of weeks it was valid.
i hope i was more clear now about the problem i have.
thank you
- OwenAuger
Thanks for that.
It looks like the problem is the VAR/RETURN syntax.
If you use VAR to store the result of your existing measure like this, it actually just evaluates once and locks in a single value, evaluated in the overall filter context (i.e. not per week).
We need the validobservation measure to be re-evaluated for every iteration of the SUMX, so the SUMX should refer to a measure (or equivalent expression wrapped in CALCULATE), not a variable.
I would say the cleanest way to do this is to keep your first measure as you have defined it (since you know it's working at the Week level), and call it say validobservation.
Then your new measure should be:
Valid Week Count = SUMX ( VALUES ( TimeTable[Week] ), [validobservation] )That is, the second argument of the SUMX should be a measure, not a variable.
Does that help?
Regards,
Owen
yes it works well now!
thank you very much