Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
HI All
My data table is
PERSON_NUMBER | NOS | ANSWER |
1234 | FF2 How you develop yourself | Additional Information Required |
6681 | FF2 How you develop yourself | Additional Information Required |
1234 | FF4 Resolve Operational Incidents | Additional Information Required |
6681 | FF4 Resolve Operational Incidents | Additional Information Required |
1234 | FF4 Resolve Operational Incidents | Additional Information Required |
6681 | FF4 Resolve Operational Incidents | Required Standard Achieved |
6681 | FF4 Resolve Operational Incidents | Required Standard Achieved |
6681 | FF4 Resolve Operational Incidents | Required Standard Achieved |
6681 | FF4 Resolve Operational Incidents | Required Standard Achieved |
6681 | FF4 Resolve Operational Incidents | Required Standard Achieved |
6681 | FF4 Resolve Operational Incidents | Required Standard Achieved |
6681 | FF4 Resolve Operational Incidents | Required Standard Achieved |
6681 | FF4 Resolve Operational Incidents | Required Standard Achieved |
6681 | FF4 Resolve Operational Incidents | Required Standard Achieved |
6681 | FF4 Resolve Operational Incidents | Null |
6681 | FF4 Resolve Operational Incidents | Null |
6681 | FF4 Resolve Operational Incidents | Null |
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
Solved! Go to Solution.
Gary, please try this:
NOS_FF4 = CALCULATE( COUNTROWS( Oracle_V2),
FILTER( Oracle_V2, CONTAINSSTRING(Oracle_V2[NOS], "FF4" ) &&
Oracle_V2[ANSWER] = "Required Standard Achieved" ))
Hope this gets you what you are looking for.
Regards,
Gary, please try this:
NOS_FF4 = CALCULATE( COUNTROWS( Oracle_V2),
FILTER( Oracle_V2, CONTAINSSTRING(Oracle_V2[NOS], "FF4" ) &&
Oracle_V2[ANSWER] = "Required Standard Achieved" ))
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
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
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
As soon as i add the card with the measure
Using
I really do appreciate the help 🙂
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
Take care, Good Luck, and if unable to resolve, suggest reposting to start a new thread on Monday.
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....
Dont know if this helps any?
Highlight yellow when clicked on returns value 9
The selected and other one thats not returns 18?
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |