cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 48 members 870 guests
Please welcome our newest community members: