Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Scocal123
Helper I
Helper I

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
AItem 1
AItem 2
BItem 3
CItem 2
DItem 2
EItem 3
FItem 3
FItem 1

 

This translates into a bar chart like this via a distinctcount measure:

Scocal123_0-1690375056721.png

 

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!

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1690512460494.png

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. 

View solution in original post

1 REPLY 1
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1690512460494.png

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.