Hi everybody,
It's my first question on this forum.
So, what I'm trying to do is compare each value in my complete data set to a subset, to sum up how many distinct values are missing from that subset. This sum will then go into a stacked bar chart showing user statistics, as a measure.
Here's the formula I wrote, so far:
Sum(
If(
UserName = //this should be from my full set
If( //in this if-bloc I'd like to get a set of the users' names that did a transaction within a month. The month is a dimension on the chart
Aggr(Count(TransactionID), UserName, TransactionMonth) > 0
,UserName //here the names should be returned
,'0' //If there aren't any, return nothing
)
,0 //if the current username from the full set matches any of the returned, then I don't want to count them
,1 //if there is no match, i.e. the user didn't do a transaction, increment the sum
)
)
I have other measures for users who are in the month. But I can't use the same formula with different parameters, as the Aggr function only works with the available data; so I need to involve the full set to count how many are missing.
Any help is appreciated.
Thanks in advance,
Sven
