CODEWITHSUNDEEP
powerbidaxpowerbi-desktopdaxstudio
Rahul
Rahul

Reputation: 537

Date diff using dates in the same column DAX

I have a dataset where I am trying to calculate the user response time to dealer note using the note date of every action.

Here is my sample data look like, I've calculated this in hive query using lag, lead, and min window functions, but my user wants to see this in Power BI.

This is what I tried so far.

I've created a "user note date" measure to get the first response of the User response

user note date = CALCULATE(MIN(Query1[Note Date]),ALLEXCEPT(Query1,Query1[incident],Query1[Action Type]),
LEFT(Query1[lastuser],1) in {"U"} )
 
Dealer Note Date  = 
CALCULATE(
MIN(Query1[pdate]),
FILTER(ALLEXCEPT(Query1,Query1[incident],Query1[action_type]),
Query1[action_type] in {
"DLR_CUST_Update"
))

I am getting this error from Dealer Note Date Measure, I am not understanding what's wrong with the above calculation.

error: A single value for column 'Action Type' in table 'Query1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min

Here is my sample data

enter image description here

Upvotes: 0

Views: 332

Answers (1)

msta42a
msta42a

Reputation: 3741

Your column in calculation for [Dealer Note Date] is query1[action_type] or Query1[Action Type]??

You cant access column Query1[action_type] in [Dealer Note Date], because you are excluding it in ALLEXCEPT

Dealer Note Date  = 
CALCULATE(
MIN(Query1[pdate]),
FILTER(ALLEXCEPT(Query1,Query1[incident],**Query1[action_type]**),
Query1[action_type] in {
"DLR_CUST_Update"
))

Upvotes: 1

Related Questions