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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
CMajka8
Helper I
Helper I

DAX Help - Excluding Some Filters on the Page

Hello Community - 

 

TLDR; I am looking for a DAX measure that will calculate the total distinct count of companies in a column, while ignoring certain slicers on the page.

 

I am creating a dynamic text title that says something like "Company X ranks 13 out of 331 companies in Premium Written during the year." I am looking for a DAX formula that can help me count the total number of companies (331) for the year selected. The page has a Year slicer and a Company Name slicer. Therefore, the measure would need to ignore the Company Name slicer, but evaluate the Year selected. 

So far, I have this, but it gives me the total count for all years, regardless of the Year selected:

Loss Line Chart Title =
IF (
ISFILTERED('Company DIM'[Company Name]),
CONCATENATEX ( VALUES ('Company DIM'[Company Name] ), 'Company DIM'[Company Name], ", " ) & " ranks " & [RANK Loss Ratio] & " out of " & [PC Counts] & " in Loss Ratio",
"All selected companies"
)
 
The measure for the counts is this:
PC Counts = CALCULATE( DISTINCTCOUNT('Data FACT'[PC]), ALLEXCEPT( 'Data FACT', 'Data FACT'[PC] ))
 
Thanks!
Chris
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@CMajka8 , use all, allselected, removefilters

 

PC Counts = CALCULATE( DISTINCTCOUNT('Data FACT'[PC]), all( 'Data FACT'))

 

or

PC Counts = CALCULATE( DISTINCTCOUNT('Data FACT'[PC]), allselecetd( 'Data FACT'))

 

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
CMajka8
Helper I
Helper I

I think I've solved it - thanks for you help. 

PC Counts = CALCULATE( COUNT('Data FACT'[PC]), ALL( 'Company DIM'[Company Name]))
 
This gets me the correct total count based on the year selected and doesn't consider the Company Name slicer. 
amitchandak
Super User
Super User

@CMajka8 , use all, allselected, removefilters

 

PC Counts = CALCULATE( DISTINCTCOUNT('Data FACT'[PC]), all( 'Data FACT'))

 

or

PC Counts = CALCULATE( DISTINCTCOUNT('Data FACT'[PC]), allselecetd( 'Data FACT'))

 

https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thank you. However, that appears to only give me a count of 1, based on the selction made in the Company Name slicer. I want to ignore that selection and get a total count of companies.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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