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:
