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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bryan_d
Regular Visitor

Help with counting

Hi there

 

I can't seem to get this to work - hoping you can help!

 

I have the following columns in a large table extracted from print logs:

 

DateGenerated     Time     Hour     Environment

1.2.2016               10:14     10         LEGACY

1.2.2016               11:15     11          NEW

 

The columns have much more information, but essentially I just want to count the number of times "LEGACY" & "NEW" appear on a certain date. Ideally, I would also be able to count date & hour - but I don't want to be greedy!

 

This is all to produce a % of total for each day.

 

Hope you can help! Thanks

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@bryan_d 

 

Here's the example I created

Counting Table.png

 

The result looks like this

Counting Result.png

 

Here are the Measures I used

LEGACY # = COUNTROWS(FILTER(Table1, Table1[Environment]="LEGACY"))
LEGACY % = DIVIDE([LEGACY #], [TOTAL #], 0)
NEW # = COUNTROWS(FILTER(Table1, Table1[Environment]="NEW"))
NEW % = DIVIDE([NEW #],[TOTAL #],0)
TOTAL # = [LEGACY #] + [NEW #]

 

Hope this helps!

View solution in original post

7 REPLIES 7
SabineOussi
Skilled Sharer
Skilled Sharer

Hello everyone,

 

I'm a bit confused here.

Doesn't the below calculated measure give the same results regardless of how many environment types you have? That way if you have more than two environments, the percentage will be calculated using only one query rather than one or two for each type.

Environment % =
DIVIDE(COUNTROWS(Table) ,

CALCULATE(
COUNTROWS(Table),
ALL(Table[Environment])
))

 

You then create a matrix where you put your date as rows, environment as columns, environment summarized as count and the calculated environment % as values.

 

Does it also work?

Sean
Community Champion
Community Champion

@bryan_d 

 

Here's the example I created

Counting Table.png

 

The result looks like this

Counting Result.png

 

Here are the Measures I used

LEGACY # = COUNTROWS(FILTER(Table1, Table1[Environment]="LEGACY"))
LEGACY % = DIVIDE([LEGACY #], [TOTAL #], 0)
NEW # = COUNTROWS(FILTER(Table1, Table1[Environment]="NEW"))
NEW % = DIVIDE([NEW #],[TOTAL #],0)
TOTAL # = [LEGACY #] + [NEW #]

 

Hope this helps!

That's great Sean - thanks so much!

 

Quick question - just wondering if you manually created the entries in the 'DateGenerated' & 'Hour' columns, or were these automatically populated?

 

Thanks

 

Bryan

Sean
Community Champion
Community Champion

@bryan_d DateGenerated and Time yes manually I just created a small table to show you the result - Hour derived from time

 

Anyway the problem with the above formula suggested although it works in a Matrix - it has more limited use

 

How are you going to display just the Legacy # with a Card? (not multi-row card)

 

Can you create a Line and Clusted Column Chart?

Sorry, yes distinct count only works if you have a unique row identifer.  Your countrows formula are the best for your data.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.
Anonymous
Not applicable

Hi @bryan_d,

 

   you can use the COUNTA() function.

I'm sorry @MattAllington I tried with the DISTINCTCOUNT() function, but I simply got 2 referred to the two different environments (as you can see from the screenshot).

 

counting.png

Let Me Know If It Works.

 

#I'M Not An Expert#

MattAllington
Community Champion
Community Champion

Put dateGenerated in rows in a matrix (or on a chart axis or what ever).  Then write 

 

=DISTINCTCOUNT(yourTableName[Environment])

 

same applies for the others 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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