Dynamic Date Filtering to get Max Values

[Anonymous]

I have a table (PolicyData) that contains the following columns:

Row Number	Policy Number	Logical Order	Effective Date
1	P1	1	2021/03/01
2	P1	2	2022/03/01
3	P2	3	2021/03/01
4	P3	4	2017/01/01
5	P3	5	2019/01/01
6	P4	6	2020/08/21
7	P4	7	2021/02/01
8	P4	8	2022/04/01

I want to produce a set of results in a table visualization such that only records corresponding to the MAX Logical Order for a given Policy are returned, depending on the Effective Date range I select in the date slicer in the UI.

For example, if I select the period:  2020/01/01 – 2022/01/01, I should get the records as follows:

Row Number	Policy Number	Max Logical Order	Effective Date
1	P1	1	2021/03/01
3	P2	3	2021/03/01
7	P4	7	2021/02/01

If I select period 2020/01/01 – 2022/05/01, I should get the following:

Row Number	Policy Number	Max Logical Order	Effective Date
2	P1	2	2022/03/01
3	P2	3	2021/03/01
8	P4	8	2022/04/01

I computed a Max Logical Order Flag by using the following Measure and formula:

Max Order Measure = Calculate(Max(PolicyData[LogicalOrder]))

Max Logical Order Flag = if(CALCULATE([Max Order Measure],FILTER(PolicyData,'PolicyData'[Policy Number]=earlier('PolicyData'[Policy Number])))=PolicyData[Logical Order],"Yes","No")

 This works only the entire table but not on a subset of the date range if I use the Max Logical Order Flag = “Yes” in the UI filters.

I have unsuccessfully tried using the AllExcept or AllSelected functions within the formulas above.

I would appreciate some help in this regard. Thanks in advance.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Row Number	Policy Number	Logical Order	Effective Date	Transaction Name	Premium
1	P1	1	2021/03/01	Renewal	100
2	P1	2	2022/03/01	Amendment 1	500
3	P2	3	2021/03/01	Renewal	400
4	P3	4	2017/01/01	Endorsement	200
5	P3	5	2019/01/01	Renewal	300
6	P4	6	2020/08/21	Endorsement	900
7	P4	7	2021/02/01	Amendment 2	700
8	P4	8	2022/04/01	Renewal	400

Thanks for the response and the sample pbix. The solution you propose works for the subset of the table I pasted but not for the whole table (which includes several columns some of which are numeric and others text). I omitted them from my original example, but I have updated the table with a couple of those columns: Transaction Name and Premium. I only want to see the results corresponding to the max of the logical order as I had mentioned in my original post, based on the date selection. However, when I added ‘Transaction Name’ and ‘Premium’ to the table Visualization, I got more records. How do I get only the records corresponding to the max values of the logical order?

[Ashish_Mathur]

Hi,

Write these measures:

T name = if(isblank([Max effective date]),blank(),CALCULATE(MIN(Data[Transaction Name]),DATESBETWEEN('Calendar'[Date],[Max effective date],[Max effective date])))

Prem = if(isblank([Max effective date]),blank(),CALCULATE(MIN(Data[Premium]),DATESBETWEEN('Calendar'[Date],[Max effective date],[Max effective date])))

Hope this helps.

[lbendlin]

You need to switch your thought process from individal cells to collections. In this case colllections of all Policies that were active in the specified date range (which needs to come from a disconnected table, btw).  Then for each of the Policies you calculate the max logical order entry. And lastly you set the visibility flag for each filter context item according to the result.

[lbendlin]

Or you could choose a simpler approach where you DO connect the Dates table to the fact table, and use the built-in aggregations and a small measure

See attached for an example.

[Anonymous]

Row Number	Policy Number	Logical Order	Effective Date	Transaction Name	Premium
1	P1	1	2021/03/01	Renewal	100
2	P1	2	2022/03/01	Amendment 1	500
3	P2	3	2021/03/01	Renewal	400
4	P3	4	2017/01/01	Endorsement	200
5	P3	5	2019/01/01	Renewal	300
6	P4	6	2020/08/21	Endorsement	900
7	P4	7	2021/02/01	Amendment 2	700
8	P4	8	2022/04/01	Renewal	400

Thanks for the response and the sample pbix. The solution you propose works for the subset of the table I pasted but not for the whole table (which includes several columns some of which are numeric and others text). I omitted them from my original example, but I have updated the table with a couple of those columns: Transaction Name and Premium. I only want to see the results corresponding to the max of the logical order as I had mentioned in my original post, based on the date selection. However, when I added ‘Transaction Name’ and ‘Premium’ to the table Visualization, I got more records. How do I get only the records corresponding to the max values of the logical order?

[lbendlin]

This additional information would take the proposed solution back to what I originally mentioned. 

Please provide sanitized sample data that fully covers your issue. 
Please show the expected outcome based on the sample data you provided. 
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

My dataset contains 56 Columns and a lot of confidential information, so it is not practical for me to paste that amount of mocked up data here unless I can add an attachment (which I cannot do for some reason) - (didnt see any icon in the reply box that lets me do that and also tried the drag and drop unsuccessfully). 

Nevertheless, I am fairly confident that if you were to elaborate on the process you indicated in your original response with a pbix example if possible, using the two additional columns I provided, I would be able to extend the logic to the other columns as required. 

The result I am looking for when I select the effective date range:  2020/01/01 – 2022/01/01 is as follows:

Row Number	Policy Number	Max Logical Order	Effective Date	Trans Name	Premium
1	P1	1	2021/03/01	Renewal	100
3	P2	3	2021/03/01	Renewal	400
7	P4	7	2021/02/01	Amendment 2	700

If I select period 2020/01/01 – 2022/05/01, I should get the following:

Row Number	Policy Number	Max Logical Order	Effective Date	Trans Name	Premium
2	P1	2	2022/03/01	Amendment 1	500
3	P2	3	2021/03/01	Renewal	400
8	P4	8	2022/04/01	Renewal	400

Thanks again for taking the time to help me resolve this.

[lbendlin]

see attached for the modified measure. It is now a visual level filter.

[Anonymous]

The attached does not work for the first result case I mentioned above (2020/01/01 - 2022/01/01). It works for the second condition (2020/01/01 - 2022/05/01).

[lbendlin · Accepted solution]

Yes, bad measure. Change to 

Measure = 
var p =SELECTEDVALUE('Table'[Policy Number])
var l =SELECTEDVALUE('Table'[Logical Order])
var lm=CALCULATE(max('Table'[Logical Order]),allselected(),'Table'[Policy Number]=p)
return if (l=lm,1,0)

[Anonymous]

Thanks. This solution worked for me. Appreciate all the assistance👍