Forum Discussion
Count rows based on data from a Measure
I have a measure so that my Column in my table "Letter Due to Supervisor" shows up blank/no date if in the Descritpion shows "Letter Wavied". Trying to get an accurate count of how many rows do have...
Table looks like this:
I have Measure to high light "Letter Wavied" in blue:
Measure 4 = IF(CONTAINSSTRING(SELECTEDVALUE('Legal Files'[Description_1]),"Letter Waived"),"blue","black")
And Measure to make date for Letter Due to Supervisor to be blank:
Letter Due to Supervisor Measure = IF(CONTAINSSTRING(MAX('Legal Files'[Description_1]), "Letter Waived")," ", MAX('Legal Files'[Letter Due to Supervisor]))
I need the count to correctly count how many rows have a Date in the Letter Due to Supervisor.
Does this help?
Not really. You're still showing us the viz table and we need a source table to understand the problem and help you with that. What I've understend from this one is that you have something like this:
| Date | Description_1 | Division |
| 2022-12-28 | Sample description | A |
| 2022-12-19 | Another description | A |
| 2022-12-20 | Dummy text | A |
| 2022-12-21 | Letter Waived | B |
| 2022-12-20 | Another description | B |
| 2022-12-19 | Sample description | B |
And you want to achive something like this:
| Max date per division | Max of Description_1 | Division | Count rows with max date (?) |
| 2022-12-20 | Dummy text | A | 3 |
| Blank because any of description for division B has description_1 Letter Waived | Letter Waived | B | 0 |
| SUM: | 3 |
Is that correct?
Yea I can't share the data source because it has PPI. I'm wanting the count to be a separate visual. But I can try this forumal to see if that will work.
What forumla did you use to get the count?
Create 2 columns:
LetterWaived Flag = IF(CONTAINSSTRING('Sample'[Description_1];"Letter Waived");1;0)LetterWaived Flag Mark =var division = [Division]return SUMX(FILTER('Sample';'Sample'[Division]=division);'Sample'[LetterWaived Flag])Then the measure is:Count of rows affeted by Letter Waived = SUM('Sample'[LetterWaived Flag Mark])You just need to change a logic to not affected.And for the description I think you have wrong formula, because max for strings correspondes to a sentence that has later letter in aphabet. Let me show you difference:What am I doing wrong? I created a new column and get this error.