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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mypowerbi1
Helper III
Helper III

remove filter in row context

Hi,

I am using a measure on Card and table visuals. The measure is giving correct value when using it on the card visual but due to row context, it divide the values when placing this measure on table visual along with some fields. 

I need aggregated values on table visual also irrespective of row values of fields used on the table.

 

Count =
var CNo = SELECTEDVALUE('cfs'[CNumber],BLANK())
var PCount = CALCULATE( DISTINCTCOUNT ('pfs'[CNumber]) , 'pfs'[CNumber] <> CNo, ALLEXCEPT('pfs',[Stage]))
Return
           PeerCount
 
when placing this measure "Count" with the field "Stage" on a table visual, it divides the values in row context while it should be 5 for all values of Stage.
 mypowerbi1_1-1654605184075.png

 

Thanks


 

1 ACCEPTED SOLUTION
AilleryO
Memorable Member
Memorable Member

Hi,

 

Not 100% sure of what you expect but if I get it right using ALL instead of ALLEXCEPT should do the trick :

Count =
var CNo = SELECTEDVALUE('cfs'[CNumber],BLANK())
var PCount = CALCULATEDISTINCTCOUNT ('pfs'[CNumber]) , 'pfs'[CNumber] <> CNo, ALL('pfs',[Stage]))
Return
           PeerCount
 
Even if you want to get rid of all filters on table pfs :
 
Count =
var CNo = SELECTEDVALUE('cfs'[CNumber],BLANK())
var PCount = CALCULATEDISTINCTCOUNT ('pfs'[CNumber]) , 'pfs'[CNumber] <> CNo, ALLEXCEPT('pfs'))
Return
           PeerCount
 
Hope it helps
 

View solution in original post

4 REPLIES 4
v-cgao-msft
Community Support
Community Support

Hi @mypowerbi1 ,

 

Please try this measure.

Count = 
VAR CNo =
    SELECTEDVALUE ( 'cfs'[CNumber], BLANK () )
VAR PCount =
    CALCULATE (
        DISTINCTCOUNT ( 'pfs'[CNumber] ),
        FILTER ( ALL ( 'pfs' ), 'pfs'[CNumber] <> CNo )
    )
VAR PeerCount =
    CALCULATE ( PCount, ALL ( 'pfs'[CNumber] ), ALLSELECTED ( 'cfs'[CNumber] ) )
RETURN
    PeerCount

If this doesn't work, please consider providing a sample of the data without privacy.

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

AilleryO
Memorable Member
Memorable Member

Hi,

 

Sorry my bad, should be :

Count =
var CNo = SELECTEDVALUE('cfs'[CNumber],BLANK())
var PCount = CALCULATEDISTINCTCOUNT ('pfs'[CNumber]) , 'pfs'[CNumber] <> CNo, ALL('pfs',[CNumber]))
Return
      PeerCount

 Assuming CNumber is the peer number you need to remove to aggregate.

Or :

Count =
var CNo = SELECTEDVALUE('cfs'[CNumber],BLANK())
var PCount = CALCULATEDISTINCTCOUNT ('pfs'[CNumber]) , 'pfs'[CNumber] <> CNo, ALLEXCEPT('pfs',[Stage]))
Return
      PeerCount

 

Could be another solution, to keep filters on Stage (assuming stage is the column with High, Low, Verbal).

Hope it helps

AilleryO
Memorable Member
Memorable Member

Hi,

 

Not 100% sure of what you expect but if I get it right using ALL instead of ALLEXCEPT should do the trick :

Count =
var CNo = SELECTEDVALUE('cfs'[CNumber],BLANK())
var PCount = CALCULATEDISTINCTCOUNT ('pfs'[CNumber]) , 'pfs'[CNumber] <> CNo, ALL('pfs',[Stage]))
Return
           PeerCount
 
Even if you want to get rid of all filters on table pfs :
 
Count =
var CNo = SELECTEDVALUE('cfs'[CNumber],BLANK())
var PCount = CALCULATEDISTINCTCOUNT ('pfs'[CNumber]) , 'pfs'[CNumber] <> CNo, ALLEXCEPT('pfs'))
Return
           PeerCount
 
Hope it helps
 

@AilleryO , I have been tried with All also but it doesn't remove the filter and ALLEXCEPT need minimum 2 argument.

ALLEXCEPT('pfs')

I need aggregated values in each row like this:

mypowerbi1_0-1654608188863.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors