Forum Discussion
Measure to exclude values split by another column
Hello,
Struggling to describe my issue here so have put detail onto Excel for simplicity. I have a table like the below:
| ID | Item |
| A | Item 1 |
| A | Item 2 |
| B | Item 3 |
| C | Item 2 |
| D | Item 2 |
| E | Item 3 |
| F | Item 3 |
| F | Item 1 |
This translates into a bar chart like this via a distinctcount measure:
For the purposes of this chart, I have then created a simple measure to exclude all items < 3 from the chart (the measure was required because my PBI version has a legend, so I had to use the ALL function to get PBI to ignore the legend as part of the filtering), meaning 'item 1' would disappear from the chart. This works fine.
I now need to add a card visual which provides the total count of all the individual items in the bar chart that have a distinctcount of >= 3, but takes into account the split by item in the count so that it removes the 'item 1' values, to give a total of 6. Do you know how best to do this? I need it to dynamically exclude items from the 'Item' column in the distinctcount if they are < 3.
Anything I have tried so far results in me retaining all of the values, i.e. the count is 8 instead of 6
Any help greatly appreciated!
Hi Scocal123 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
Measure = var a=SUMMARIZE(ALLSELECTED('Table'),[Item],"Distinct",DISTINCTCOUNT('Table'[ID ])) var b=SELECTCOLUMNS(FILTER(a,[Distinct]>=3),"item",[Item]) return COUNTROWS(FILTER('Table',[Item] in b))(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi Scocal123 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a measure.
Measure = var a=SUMMARIZE(ALLSELECTED('Table'),[Item],"Distinct",DISTINCTCOUNT('Table'[ID ])) var b=SELECTCOLUMNS(FILTER(a,[Distinct]>=3),"item",[Item]) return COUNTROWS(FILTER('Table',[Item] in b))(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.