DAX Measure Sum a column base on Grouping, Max date and Condition

[mlovejoy]

Hello community, 

I'm trying in a DAX measures to retrieve the number of Status for each unique key by doing the sum of criteria column.

- Rules are always to keep the Max date for each unique key groups

- Filtering criteria = 1

- if status have the same date and are both "valide" then I need to say keep first Cancelled, then confirmed, then accepted,... to get only 1 status by unique key

Any genius here :-)?

Regards,

Mark

 

 

[v-yueyunzh-msft]

Hi , @mlovejoy 

Thank you very much for the sample data, and based on your description, I don't quite understand your needs.

For your sample data:

When we filter the [Criteria]=1, the status in [Unique key]=1 has no "Accepted", can you detailed explain the logic in this?

And why [Unique key]=2  , the status = "Cancelled"?Can you describe your calculation process in detail?

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[mlovejoy]

Hi @v-yueyunzh-msft , @tamerj1 ,

sorry my sample wasn't correct,...

The business case is the following:

I have a dataset that show only the current status (acc, conf, cancel,..) but it keeps a date in columns when the status was change (1 column for acceptance date, 1 column for cancelation date. Every row of the dataset has a unique key.

I have appended all dates in 1 column -> creating duplicates of the unique key. 

 

By using time intelligence I can recreate the past and know how many where accepted, confirmed, cancelled at each date (reason I need a measure).

Calculation:

-Get the latest date of each grouping of Unique key dynamically based on date slicer

- If a grouping has 2 valid status with same max date then prioritize, 1st cancelled, 2nd Accepted,... (if this criteria is to complexe I can find a turnarround)

- In order to retrieve the number of occurence by status I need to sum the criteria column has it remove dupplicates. (specific to the data)

To sumup: I need to dynamically group by unique key and filter everygroup to keep the latest date and sum the criteria column to have the number of occurence per status at a given time.

I hope it makes it more clear?

Thank for you help and time,

Mark

 

[v-yueyunzh-msft · Accepted solution]

Hi , @mlovejoy 

Thanks for your quick response and detailed explanation for your need !

According to your description, you want to calculate the [Criteria]=1 and the date is the latest date group ny unique key.And when it have one more status (1st cancelled, 2nd Accepted).

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can create a measure like this:

Measure = var _t1= FILTER(ALLSELECTED('Table'),'Table'[Criteria]=1)
var _latest_date =FILTER( ADDCOLUMNS(_t1 , "latestDate" , var _key = [Unique Key] return MAXX(FILTER(_t1 , 'Table'[Unique Key]=_key ) ,[Date])) , [Date] = [latestDate])
var _t2 = SUMMARIZE(_latest_date,[Unique Key],[Status],[Criteria]) 
var _t3 = ADDCOLUMNS(_t2 , "count" , var _key=[Unique Key] return COUNTROWS(FILTER(_t2,[Unique Key]=_key)))
var _t4 =FILTER( ADDCOLUMNS(_t3, "flag", IF([count] <> 1 && [Status]="Accepted" ,0 ,1)),[flag]=1) 
var _curstatus = MAX('Table'[Status])
return
COUNTROWS(FILTER(_t4,[Status]=_curstatus))

Then we can put it in the visual and we can meet your need, the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[mlovejoy]

Hello @v-yueyunzh-msft  , 

thanks for you help and sorry for my late reply, I was not working during he past 2 weeks. 

I've tested your solution , however I see 2 problems at the end of the formula:

1st: var _t4 = FILTER(ADDCOLUMNS(_t3 , "flag" , IF([count] <> 1 && 'Test Append Tables'[Status] ="Accepted" , 0 , 1)) , [flag] =1) // this variable excludes only "Accepted" status, however we can have 2 or more duplicates with different status. -> It means there needs to be a hierachy of selection. If duplicates than first keep Cancelled, than Deferred than Accepted. I would imagine a combination of Switch(true(), to create the hierarchy and MAX or MIn to selected the correct status in the hierarchy.

2nd: In a Matrice the Total doesn't work. Any ideas why?

Thanks again for your help, this time I will be ready to accept your solution 🙂

Regards,

Mark

 

[tamerj1]

Hi @mlovejoy 

please double check the expected results as they seem not to match the description (at least for key 1 and key 2)