Forum Discussion
Counting specific values a measure (not a column) can dynamically assume
Hi All,
I am trying to fix a piece of DAX code in order to get a correct count of certain ranks.
Here is the problem:
I am evaluating performances starting from a fact-table presenting - of course - also years and months columns.
My measure, which is clearly dymanic on whatever is the selected period in a month/year slicer, need to be "counted".
I mean: the possible results of this measure are "Rank1", "Rank2", "Rank3", "Rank4", "Rank5".
And I would like, for example, to viasualize on a card how many people are in "Rank1" - whatever is the selected period.
Issue - I tried to use CALCULATE and FILTER, like this:
Total Rank 1 = CALCULATE([Total Evaluations],FILTER(MyFactTable,[MyMeasure]="Rank1"))
Where: [Total Evaluations] = COUNTROWS(MyFactTable).
Well, the problem is that this does not scale with multiple selected months (whereas it works for a single month).
Reason:
FILTER is an iterator and the context is altered! So, for example, if we have:
5 people scoring "Rank1" in June and 6 people scoring Rank1" in July, this approach returns me 11 people scoring "Rank1" over a period of 2 month, which is NOT correct - due to the fact that the evaluation is a measure: it is dynamic and it may change if performed over a period of 2 months (June+July).
Meaning: an employee can be "Rank1" in both the single months, but "Rank2" if evaluating his/her numbers over the whole 2 months.
Therefore, my question is: how can I get this fixed?
7 Replies
v-cherch-msftMicrosoft Employee
Hi Anonymous
It seems you may try to use SELECTEDVALUE Function. If it is not your case, please share some simplified data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
Cherie
- dobregon
Impactful Individual
Hi,
Maybe with an example it will be more easy but... as i understand your question you can try to do:
CALCULATE(COUNT(MyFactTable[Total Evaluations]),FILTER(MyFactTable,MyFactTable[MyMeasure] = "Rank 1"))
Hi Guys,
Many Thanks for your kindness.
It might be tricky to get a fake dataset for better depicting the problem.
However, SELECTEDVALUES looks like to be consistently available only when coping with calculated columns (non measures).
We are still stuck. Reason:
FILTER is an iterator and the measure [MyMeasure] is then evaluated - row by row - just like a Calculated Column!
This is wrong, becasue - for example - I know, for sure, that:
- in June, we had 3 Rank-1 employees;
- in July, we had 6 Rank-1 employees;
- in the bimester (June+July), we had 4 Rank-1 employees (by crunching their numbers on an extended period, not just a month).
The issue is that these mesaures (both) returns me 9 Rank-1 employees for the bimester, which is incorrect, because we do not want the "calculated column like" behaviour (fetching 3 from June and 6 from July - since we have one record per employee per month), we would like to be able to scale up to a bimester as well!
So, it is all about to find a way to extend that filter context - from a row by row context to the selected period.
Of course, Calculate + FILTER seems to be the wrong way.
However, by trying with SUMX + SUMMARIZE (hence, getting rid of the Month/Year within the on-fly re-calculated table) we would lose the possibility to dynamically slice data with a month/year slicer!
We are stuck...
- dobregon
surely you need to do a Calculated measure but if i can't see an example it is really difficult to understand. Maybe if you do an excel with some exmaple and example of the result that you are looking for... maybe i can help you
- dobregon
the formula that you use seems ok and the result should be
Total Rank 1 = 3; becasue there are 3 diferents employee name related to Rank 1. But everthing depends of the visual that you select
The result 3 will be for the total of the data source, but if you want for each Year-month, you need to put a filter visual Month in porder to put N visuals for each month and give you the total rank 1 for each mont.Ore one visual table with YEAR-MONTH and Total Ranks. in that case the measure will do the calculation for each month.
Hi!
Thank you for your relentless support.
Well, indeed, this is not addressing the guy scoring Rank1 in a month, Rank 2 in the other month BUT Rank 1 over the two months.
To be honest, I am not really sure, at this point, whether [Total Evaluation] should be computed as:
- COUNTROWS(MyFActTable] or
- DISTINCTCOUNT(Employee Name]
Becasue for single-month selections, I would go with COUNTROWS(MyFActTable].
And - indeed - it is the only robust thing so far.
This question of the FILTER context - here - is a tricky one...
