Count rows based on data from a Measure

[inglexjc]

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 dates showing.  But the count is not working because the data is based on a measure.

Letter Due to Supervisor Measure = IF(CONTAINSSTRING(MAX('Legal Files'[Description_1]), "Letter Waived")," ", MAX('Legal Files'[Letter Due to Supervisor]))

How do I get it to count correctly?

[inglexjc]

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?

[bolfri]

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?

[inglexjc]

What forumla did you use to get the count?

[bolfri]

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:

[inglexjc]

What am I doing wrong?  I created a new column and get this error.

[bolfri]

I am using Polish language in DAX, so for me separator is ; 

Change all ; to , and it will be working. 🙂

[inglexjc]

That helped but the count is still wrong.  It's coming up with 83 and it should be 63

[v-yiruan-msft]

Hi @inglexjc ,

Whether your problem has been resolved? If no, could you please provide some raw data in your table 'Legal Files'(exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[inglexjc]

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.

[themistoklis]

@inglexjc 

Why are you trying to use the column with dates and not the description column:

Try measure below:

Measure = COUNTROWS(FILTER(Sheet1, Not(CONTAINSSTRING(Sheet1[Description], "Letter Waived"))))

[inglexjc]

I can have it count the Description as long as it doesn't count where it says "Letter Waived"

Using:

Measure = COUNTROWS(FILTER('Legal Files', Not(CONTAINSSTRING('Legal Files'[Description_1], "Letter Waived"))))  It's counting 995 it should only be 63.  So it looks like it's not counting the row.

[themistoklis]

can you share the full dataset with us... if it doesnt contain any sensitive data

IM saying this because it works on the sample dataset you sent

Also in you formula you use a field called Description_1 ... in your message the field is Description (with the _1)

[inglexjc]

The information has PPI so I can not share it.  But those are the columns that I need help with.

[bolfri]

Can you post here a pbix file or sample records in the table (not in the viz)?