cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Computron Frequent Visitor
Frequent Visitor

Distinct count of entries in matrix table

G'day BI'ers....

  Hope you're well.  Wondering if I can pick you're brains.

Issue:
  I am trying to sum the total number of time slots per day used (shown in the image below), to be entered into a simple card.

image.png

 

  The result should equal 12 when unfiltered, but adapt to any filter function added, changing this value accordingly.

  Is there a function or DAX measure that can be written to count this number? 

  If further evidence is please let me know what may help and I will provide it.

 

  Kind regards and thanks for your time.


  Computron.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Distinct count of entries in matrix table

I have no idea how you would want to calculate desk utilization.  I'm just here to help get you the result you're looking for. In this case, 12 for the data you provided.

 

 

DeadDeskDenom = COUNTROWS( SUMMARIZE(FILTER('WUA BI App Export', 'WUA BI App Export'[Status]="Dead"), 'WUA BI App Export'[Day], 'WUA BI App Export'[SurveyID]) )

 

Depending on how your report is set up, you may be able to remove the FILTER from within the summarize, if you have a slicer or filter that is already applying the Status="Dead" filter to the card. 

 

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Distinct count of entries in matrix table

How are the values determined in the first place? Is it some sort of sum or count?

Computron Frequent Visitor
Frequent Visitor

Re: Distinct count of entries in matrix table

Hi Cmcmahan,

  Thanks for taking an interest in my query.

  The values shown in the matrix are produced through a countrows measure:

 "DeadDeskCounter = calculate(countrows('WUA BI App Export'),'WUA BI App Export'[Desk Status] = "Dead")"
 

  Background:
  The purpose of my request is to generate a denumerator with I can calcuate the average number of dead desks in a building for a workplace utilisation study.

  Of course if I am barking up the wrong tree please let me know.

  Kind regards,

  Computron.

Super User
Super User

Re: Distinct count of entries in matrix table

I have no idea how you would want to calculate desk utilization.  I'm just here to help get you the result you're looking for. In this case, 12 for the data you provided.

 

 

DeadDeskDenom = COUNTROWS( SUMMARIZE(FILTER('WUA BI App Export', 'WUA BI App Export'[Status]="Dead"), 'WUA BI App Export'[Day], 'WUA BI App Export'[SurveyID]) )

 

Depending on how your report is set up, you may be able to remove the FILTER from within the summarize, if you have a slicer or filter that is already applying the Status="Dead" filter to the card. 

 

View solution in original post

Computron Frequent Visitor
Frequent Visitor

Re: Distinct count of entries in matrix table

Worked like a charm, thanks for the suggestion. 

(The background was only meant to flesh out why I made the request, not to influence how you would choose to solve my issue).

I'll mark this as a solution.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)