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
Highlighted
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 Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,869)