Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
Solved! Go to 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.
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 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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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
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.
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
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.
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:
Hi @elir ,
This is my PBIX file, you can see the difference.
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.
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.
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
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |