Forum Discussion
Calculated Ccolumn, Grouping and filter
I want to count how often c1 and c2 has the same values in a calculated column. The calculated column should be used in a axis from a visual. The userfilter column should be used as a row level security.
I’ve created a column with following DAX code:
counter =
CALCULATE(COUNTA('Grouping'[id]);
FILTER(ALL('Grouping') ;
'Grouping'[c1] = EARLIER('Grouping'[c1]) && 'Grouping'[c2] = EARLIER('Grouping'[c2])))
If I filter on UserB, the column returns 2. But the expected result are 1.
So I need a FILTER which returns all rows, but the return table from the filte function must apply the filter on the userfilter column.
Any ideas how I achieve this requirement?
6 Replies
I am not certain but I believe you want something like this:
Column = COUNTX(FILTER(ALL('Grouping'),[c1]=[c2] && [userfilter]=EARLIER([userfilter])),[id])I say not certain because the table I get is:
idc1c2userfilterColumn
1 A A UserA 2 2 A B UserB 3 A A UserA 2 4 A B UserA 2 So, in the data you supplied, I do not get an occurrence where c1 and c2 are equal for UserB. So...?
So, perhaps you meant this:
Column2 = COUNTX(FILTER(ALL('Grouping'),[c1]=[c2] && [userfilter]<>EARLIER([userfilter])),[id])Which returns this table:
idc1c2userfilterColumnColumn2
1 A A UserA 2 2 A B UserB 2 3 A A UserA 2 4 A B UserA 2 Or maybe this:
Column3 = COUNTX(FILTER(ALL('Grouping'),[c1]=EARLIER([c1]) && [c2]=EARLIER([c2]) && [userfilter]=EARLIER([userfilter])),[id])Which gives you this:
idc1c2userfilterColumnColumn2Column3
1 A A UserA 2 2 2 A B UserB 2 1 3 A A UserA 2 2 4 A B UserA 2 1 If none of those, can you supply your full expected results. This is why I always ask for them, see this:
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi,
I am unclear about your question. Please show the expected result.
Thanks for the replies. But nothing above match the needs.
The expected result is:
- Group all rows by c1 and c2 and count in a new column;
Id | C1 | C2 | userfilter | counter
1 | A | A | UserA | 22 | A | B | UserB | 2
3 | A | A | UserA | 2
4| A | B | UserA | 2
The column returns every time 2, because the combination c1 and c2 are two times in the resultset.
This work with this code:
counter =CALCULATE(COUNTA('Grouping'[id]);
FILTER(ALL('Grouping') ;
'Grouping'[c1] = EARLIER('Grouping'[c1]) && 'Grouping'[c2] = EARLIER('Grouping'[c2])))
If I implement a Row Level Secuity or a slicer on the column “userfilter”, and I filter on UserA, the column returns also two as the value, but the row with userB should not be in the resultset.
The expected result should be the following:
Id | C1 | C2 | userfilter | counter
1 | A | A | UserA | 23 | A | A | UserA | 2
4| A | B | UserA | 1
The statement must be work with and without a slicer or RLS.
Any ideas?
