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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gazzo1967
Helper III
Helper III

Calculate/CountRows based on 2 text values - Help With formula

HI All
My data table is

PERSON_NUMBERNOSANSWER
1234FF2 How you develop yourselfAdditional Information Required
6681FF2 How you develop yourselfAdditional Information Required
1234FF4 Resolve Operational IncidentsAdditional Information Required
6681FF4 Resolve Operational IncidentsAdditional Information Required
1234FF4 Resolve Operational IncidentsAdditional Information Required
6681FF4 Resolve Operational IncidentsRequired Standard Achieved
6681FF4 Resolve Operational IncidentsRequired Standard Achieved
6681FF4 Resolve Operational IncidentsRequired Standard Achieved
6681FF4 Resolve Operational IncidentsRequired Standard Achieved
6681FF4 Resolve Operational IncidentsRequired Standard Achieved
6681FF4 Resolve Operational IncidentsRequired Standard Achieved
6681FF4 Resolve Operational IncidentsRequired Standard Achieved
6681FF4 Resolve Operational IncidentsRequired Standard Achieved
6681FF4 Resolve Operational IncidentsRequired Standard Achieved
6681FF4 Resolve Operational IncidentsNull
6681FF4 Resolve Operational IncidentsNull
6681FF4 Resolve Operational IncidentsNull





 

I am trying to create a measure based on the content of 2 cells on a row.

The Measure formula i have got to is

NOS_FF4 = CALCULATE(
COUNTROWS(Oracle_V2),
SEARCH("FF4",Oracle_V2[NOS],,0)
&& SEARCH("Standard Achieved",Oracle_V2[ANSWER],,0)
)
 
When i apply the PERSON_NUMBER (ie 6681) as a filter/role in modelling , it is not returning the correct value (used a card for testing. )
My relationships are all working correctly 🙂
Any Help would be gratefully received 🙂
Gary
1 ACCEPTED SOLUTION
rsbin
Super User
Super User

@gazzo1967 ,

Gary, please try this:

NOS_FF4 = CALCULATE( COUNTROWS( Oracle_V2),
                     FILTER( Oracle_V2, CONTAINSSTRING(Oracle_V2[NOS], "FF4" ) &&
                             Oracle_V2[ANSWER] = "Required Standard Achieved" ))

rsbin_0-1663952042441.png

 

Hope this gets you what you are looking for.

Regards,

View solution in original post

12 REPLIES 12
rsbin
Super User
Super User

@gazzo1967 ,

Gary, please try this:

NOS_FF4 = CALCULATE( COUNTROWS( Oracle_V2),
                     FILTER( Oracle_V2, CONTAINSSTRING(Oracle_V2[NOS], "FF4" ) &&
                             Oracle_V2[ANSWER] = "Required Standard Achieved" ))

rsbin_0-1663952042441.png

 

Hope this gets you what you are looking for.

Regards,

@rsbin 
we were both right lol
Turns out it was data corruption  (provided externally)
The csv file was out of whack!

Both of our solutions worked so marked as solved

@rsbin 
Thank you for replying but that is returning 81 for me 😞

My actual Data Table 81 rows with NOS = FF4 Resolve Operational Incidents and ANSWER= Required Standard Achieved is 21 rows the others are Additional Info Required or Blank

When i apply the modelling role it just counts the 81 rows with FF4 Resolve Operational Incidents and doesnt filter down to the 9 entries with Required Standard Achieved also.

I don't get why not

@gazzo1967 ,

Please attach your pbix file if you can, after excluding any sensitive data.  I will try to have a closer look.

 

@rsbin 
Its difficult to exclude sensitive data as im using the Sharepoint Information list as my relationship

@rsbin 

Its wierd logic says it should be working 😞
Its as if the second part of the measure is being ignored!

 

This is the screen with the Modelling role as employee_id  = "6681"

It filters the record correctly

gazzo1967_0-1663954665796.png

 

As soon as i add the card with the measure

NOS_FF4_TEST = CALCULATE( COUNTROWS( Oracle_V2), FILTER( Oracle_V2, CONTAINSSTRING(Oracle_V2[NOS], "FF4 Resolve Operational Incidents" ) && Oracle_V2[ANSWER] = "Required Standard Achieved" ))
 
gazzo1967_1-1663954920654.png

 

 

@rsbin 

Using 

NOS_FF4_TEST = CALCULATE( COUNTROWS(Oracle_V2), Oracle_V2[NOS] = "FF4 Resolve Operational Incidents" )
 The result  returned is more than the rows of data
gazzo1967_0-1663956755088.png

 

I really do appreciate the help 🙂

@gazzo1967 ,

Unfortunately, I am not at all familiar with the view you are using.  I have never used Sharepoint Lists. 

If I run into an issue I can't resolve, I just start from the beginning.

1)  Do a simple COUNT of EmployeeID or COUNTROWS of the table.  Hope you get an accurate result.

2) Then add on the complexities.  ie. add the first filter condition.  Get this part right.

 = CALCULATE( COUNT(Oracle_V2[EmployeeID]), FILTER( Oracle_V2, Oracle_V2[NOS] ="FF4 Resolve Operational Incidents" )

3) Then add the second filter condition, etc....

@rsbin 
I appreciate you help.
Its late here for me so will come at it tomorrow with fresh eyes.
The only share point list i am using is the User Information lrst everything else is a CSV.
Thanks for the help 🙂
Gary

 

@gazzo1967 ,

Take care, Good Luck, and if unable to resolve, suggest reposting to start a new thread on Monday.

@gazzo1967 ,

What happens if you modify the first filter from CONTAINSSTRING to simply:
Oracle_V2[NOS], = "FF4 Resolve Operational Incidents" ?? 

Or instead of COUNTROWS, do a COUNTA(Oracle_V2[EmployeeID])

I'm grasping at straws here....

@rsbin 

Dont know if this helps any?

Highlight yellow when clicked on returns  value 9

The selected and other one thats not returns 18?

gazzo1967_0-1663957448046.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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