cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
android1 Member
Member

Counting text values in 2 different Calculated Columns

Hi,

 

I have 2 calculated Columns -> InFull = IF([ActualDuration]<[ScheduledDuration]*.85,"Not In Full","In Full") 

Punctuality = IF (OTIF[DutyTimeFrom]<OTIF[TimeFrom]-1/24*.25,"Early",IF (OTIF[DutyTimeFrom]>OTIF[TimeFrom]+1/24*.25,"Late","On Time"))

 

I need to add the total number of times that the text 'In Full' appears & "On Time". I only want to add them if both appear.

1 ACCEPTED SOLUTION

Accepted Solutions
Sean Super Contributor
Super Contributor

Re: Counting text values in 2 different Calculated Columns

@android1 This should work...

 

In Full & On Time =
COUNTROWS (
    FILTER ( 'OTIF', 'OTIF'[Punctuality] = "On Time" && 'OTIF'[InFull] = "In Full" )
)

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Counting text values in 2 different Calculated Columns

Try this:

 

InFullOnTime = CALCULATE(COUNT([SomeColumn]),FILTER(OTIF,OTIF[InFull] = "In Full"),FILTER(OTIF,OTIF[Punctuality] = "On Time"))

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

android1 Member
Member

Re: Counting text values in 2 different Calculated Columns

Thanks for your reply. Can I use Count when dealing with strings? Getting the error attached.

 

Count Error Msg.JPG

Super User
Super User

Re: Counting text values in 2 different Calculated Columns

Use COUNTA


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

android1 Member
Member

Re: Counting text values in 2 different Calculated Columns

Can you explain what column [Some Column] should refer to? I tried referencing both [In Full] & [Punctuality] 

with no joy.

Sean Super Contributor
Super Contributor

Re: Counting text values in 2 different Calculated Columns

@android1 This should work...

 

In Full & On Time =
COUNTROWS (
    FILTER ( 'OTIF', 'OTIF'[Punctuality] = "On Time" && 'OTIF'[InFull] = "In Full" )
)

View solution in original post

Sean Super Contributor
Super Contributor

Re: Counting text values in 2 different Calculated Columns

@android1 you could also just create a Matirx and use a simple Measure like in the picture

 

Countrows = COUNTROWS('Table')

 

IF - Arrival Time - Late or On Time2.png

android1 Member
Member

Re: Counting text values in 2 different Calculated Columns

Hi Sean,

 

I was sure when I saw your && suggestion that it was something I had already tried. 

But whatever, yours works perfectly. The Matrix option is very interesting.

 

Thanks a million for this.

android1 Member
Member

Re: Counting text values in 2 different Calculated Columns

Thanks to smoupre too for your input. 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 345 members 3,309 guests
Please welcome our newest community members: