DAX measure to count rows based on a slicer filter

[elir]

Hi experts,

I'm trying to create a DAX measure that will return the number of distinct values from a terget table (QuizResponses) which is filtered based on a slicer.

The measure consists of two filters:

• Value of column Date is between  Start and End dates, which are based on the slicer selection. This part of the filter works well with no issues.
• Values in column Employee in the target table (QuizResponses) should be part of the subset of another table (Reminders table which also have an Employee column). This subset is the outcome of the Reminders table with the follwoing filters:
  • SentDate is between Start and End dates, which are based on the slicer selection, just the same as for the filter above.
  • Values in column Body should include a string that is also based on the slicer selection.

The second filter - Employee value should be part of the subset of the Reminders table - is not working (neither SentDate or Body filters are not working).

I suspect that with the second filter I'm getting NULL values for the variables (see below the measure description) and as a result I'm getting the famous error message : 'A single value for column 'SentDate' in table 'Reminders' cannot be determined.'

In order to help you understand I added the DAX measure and the SQL Query which should give just the same results.

DAX Measure

NumOfResponses = CALCULATE(

DISTINCTCOUNT(QuizResponses[ID]),

FILTER(ALLEXCEPT(QuizResponses, TrainingModules[TrainingModules], QuizResponses[Employee]),

AND(

AND(QuizResponses[Date] >= DATE(SELECTEDVALUE('Calendar'[Year]), SELECTEDVALUE('Calendar'[Month]) - 1, 1),

QuizResponses[Date] <= EOMONTH(DATE(SELECTEDVALUE('Calendar'[Year]), SELECTEDVALUE('Calendar'[Month]), "1"), 0)),

QuizResponses[Employee] IN (

var _StartDate = DATE(SELECTEDVALUE('Calendar'[Year]), SELECTEDVALUE('Calendar'[Month]) - 1, 1)

var _EndDate = EOMONTH(DATE(SELECTEDVALUE('Calendar'[Year]), SELECTEDVALUE('Calendar'[Month]), "1"), 0)

var _Dates = DATESBETWEEN('Calendar'[Date], _StartDate, _EndDate)

var _Body = COMBINEVALUES(" ", SELECTEDVALUE('Calendar'[MonthName]), SELECTEDVALUE('Calendar'[Year]))

var _Table = FILTER(ALLEXCEPT(Reminders,'Reminders'[SentDate], Reminders[Body]), AND('Reminders'[SentDate] IN _Dates, CONTAINSSTRING('Reminders'[Body], _Body)))

return

_Table)

)))

SQL Query

SELECT count(distinct [ID])
FROM [QuizResponses]
WHERE SubmissionTime between '2020-11-1' and '2020-12-31' and Employee IN (
SELECT distinct [Employee]
FROM [dbo].[Reminders] R inner join Employees E ON R.EmployeePk=E.Pk
where SentDate between '2020-11-1' and '2020-12-31' and body like '%Dec 2020%')

Any help is much appreciated as I already spent a lot of time trying to solve it.

Thanks

[AllisonKennedy]

@elir What relationships exist between these three tables? To get a single value SentDate in Reminders table you may need to try 

FILTER(Reminders, Reminders[SentDate] < MAX(Date[Date]) || Reminders[SentDate] > MIN(Date[Date]) )

as one of your filter conditions. 

Use || instead of the AND() function to make your code easier to read. 

[elir]

Hi Allison,

There is a one to many relationship between QuizResponses and Employees and between Reminders and Employees based on the Employee column.

There is also a one to many relationship between QuizResponses and Calendar table (which the slicer is based on) and between Reminders and Calendar as well.

I used a date range variable between _StartDate and _EndDate so it should be the same, but I tried your suggestion to use >= _StartDate and <= _EndDate, but it didn't help.

Thanks

[v-kkf-msft]

Hi @elir ,
Try the following formula:

NumOfResponses = 
var _StartDate = 
  MINX(
    ALLSELECTED('Calendar'),
    'Calendar'[Date]
  )
var _EndDate = 
  MAXX(
    ALLSELECTED('Calendar'),
    'Calendar'[Date]
  )
var _Body = 
  COMBINEVALUES(
    " ", 
    SELECTEDVALUE('Calendar'[MonthName]), 
    SELECTEDVALUE('Calendar'[Year])
  )
var result = 
  CALCULATE(
    DISTINCTCOUNT(QuizResponses[ID]),
    QuizResponses[SubmissionTime] >= _StartDate,
    QuizResponses[SubmissionTime] <= _EndDate,
    FILTER(
      Reminders,
      'Reminders'[SentDate] >= _StartDate 
      &&'Reminders'[SentDate] <= _EndDate 
      && CONTAINSSTRING('Reminders'[Body], _Body)
    )
  )
return result

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elir]

Hi Winniz,

Many thanks for your help.

I've tried it and although there is no error now I'm getting a blank result.

I've changed the measure slighly as the EndDate should be the end of next month, but it is still the same - no results:

NumOfResponses = 
var _StartDate = 
  MINX(
    ALLSELECTED('Calendar'),
    'Calendar'[Date]
  )
var _EndDate = 
  EOMONTH(DATE(
      YEAR(MAXX(ALLSELECTED('Calendar'), 'Calendar'[Date])),
      MONTH(MAXX(ALLSELECTED('Calendar'), 'Calendar'[Date])) + 1,
      DAY(MAXX(ALLSELECTED('Calendar'), 'Calendar'[Date]))), 0
    )
var _Body = 
  COMBINEVALUES(
    " ", 
    SELECTEDVALUE('Calendar'[MonthName]), 
    SELECTEDVALUE('Calendar'[Year])
  )
var result = 
  CALCULATE(
    DISTINCTCOUNT(QuizResponses[ID]),
    QuizResponses[Date] >= _StartDate,
    QuizResponses[Date] <= _EndDate,
    FILTER(
      Reminders,
      'Reminders'[SentDate] >= _StartDate 
      &&'Reminders'[SentDate] <= _EndDate 
      && CONTAINSSTRING('Reminders'[Body], _Body)
    )
  )
return result

I used the same syntax for the start and end date to create separate measures to check it and it seems OK (see below).

As it is still blank result, I created a simple measure to count distinct ID values based on two fixed dates:

Measure = CALCULATE(
    DISTINCTCOUNT(QuizResponses[ID]),
    QuizResponses[Date] >= DATE(2020,11,1),
    QuizResponses[Date] <= DATE(2020,12,31))

 However, I'm still getting blank results and I don't know why.

best regards

Eli

[elir]

I've found the problem why it is BLANK.

There was a relationship between QuizResponses table and the Calendar table between SubmissionDate, which is DATETIME field, and Date field on the Calendar table.

Once I changed it to a relationship based on a DATE field betwen the two it is not BLANK anymore.

However, I'm getting a wrong result as opposed to what I'm getting when running the SQL query.

It should be 9 when I choose Dec 2020 on the slicer, while I'm getting 1.

So I created aanother simple measure to try to understand it:

Measure = CALCULATE(
    DISTINCTCOUNT(QuizResponses[ID]),
    QuizResponses[Date] >= DATE(2020,11,1),
    QuizResponses[Date] <= DATE(2020,12,31))

With this measure I'm getting 10 instead of 26. Quite strange.

[elir]

Hi @v-kkf-msft

Thanks for the pbix.

The model structure in your file is different from what I'm using as there is no direct relationship between Reminders table and QuizResponses table.

The Employee field in the QuizResponses table doesn't include the same data as the Body field in the Reminders table.

Body field actually includes an email content that has been sent as a reminder.

So both tables have relationship to the Employees table, and this is the reason the measure should include in the filter a check to see whether the employee in the QuizResponses table is in the list of employees that is a subset of the Reminders table, or in other words we should use the IN or ContainRows function in the filter, and I'm not sure how you implemented it in your measure.

Please see below the model structure:

[v-kkf-msft]

Hi @elir ,

This is my PBIX file, you can see the difference.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/ERYrUP_AFdZHje0nhHPBiw...

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elir]

And the last part...

I used your measure as a template to mine with some changes:

NumOfResponses = 
var _StartDate = 
  EDATE(
    MINX(
        ALLSELECTED('Calendar'),
        'Calendar'[Date]), -1
  )
var _EndDate = 
  MAXX(
        ALLSELECTED('Calendar'),
        'Calendar'[Date]
    )
var _Body = 
  COMBINEVALUES(
    " ", 
    SELECTEDVALUE('Calendar'[MonthName]), 
    SELECTEDVALUE('Calendar'[Year])
  )
var result = 
  CALCULATE(
    DISTINCTCOUNT(QuizResponses[ID]),
    QuizResponses[Date] >= _StartDate,
    QuizResponses[Date] <= _EndDate,
    QuizResponses[Employee] IN CALCULATETABLE(
      ALLSELECTED(Reminders[Employee]),
      'Reminders'[SentDate] < _StartDate, 
      'Reminders'[SentDate] > _EndDate, 
      NOT(CONTAINSSTRING('Reminders'[Body], _Body))
    )
  )
return result

You can see the part with the IN statement, but this part doesn't work as it gives back all employees no matter the filter, so it actually doesn't have any affect.

As the same number of columns should be compared between QuizResponses table and Reminders table, I've tried to use ALLEXCEPT as well as ALLSELECTED, but still this part doesn't influence the measure.

[elir]

Hi @v-kkf-msft 

Continue to my previous reply (I had to split it as it doesn't let me post all together), 

I've added a link to a sample of data if it will be easier for you:

https://drive.google.com/file/d/1Ipcup5FNaWMIYd_gYiUlGiFrjj0vq9pZ/view?usp=sharing

[v-kkf-msft · Accepted solution]

Hi @elir ,

Try the following formula:

NumOfResponses = 
var _StartDate = 
    MINX(
        ALLSELECTED('Calendar'),
        'Calendar'[Date]
    ) 
var _EndDate = 
  MAXX(
        ALLSELECTED('Calendar'),
        'Calendar'[Date]
    )
var _Body = 
  COMBINEVALUES(
    " ", 
    SELECTEDVALUE('Calendar'[MonthName]), 
    SELECTEDVALUE('Calendar'[Year])
  )
var result = 
  CALCULATE(
    DISTINCTCOUNT(QuizResponses[ID]),
    QuizResponses[Date] >= _StartDate,
    QuizResponses[Date] <= _EndDate,
    FILTER(
        Reminders,
        'Reminders'[SentDate] >= _StartDate
        &&'Reminders'[SentDate] <= _EndDate
        && CONTAINSSTRING('Reminders'[Body], _Body)
    )
  )
return result

This is my PBIX file.

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-lazhang_microsoft_com/EWH5THAei1NMgVsGj7U_4U...

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[elir]

Hi @v-kkf-msft 

That's fantastic. It's working.

All I had to do besides using your measure was to disconnect the relationship between the QuizResponses table as well as the Reminders table and the Calendar table.

Thanks a lot for your help. Much appreciated.

All the best

Eli