Conditional filter by importance

[Big_Trucks]

Hi All,

I have looked extensively for help on this item and have been unable to find something similar.

What I want to do is be able to sort out from a table a specific set of rows according to a set of criteria using a measure. I have compiled all of this information into a single table in BI. As you can see in my example table below, I have accepted and declined quotes with date stamps. I want to take only one estimate in the last 6 months. If there is more then one, I want to exclude the others from my final result. Within that, I want to only take accepted estimates as priority, and if they are all declined, then take the most recent item. The end goal really is to only have one estimate for each car number in a 6 month timeframe.

DATE	ACCEPTED/DECLINED	ESTIMATE NUMBER	Car Number	AMOUNT $
1/2/2018	ACCEPTED	11	445	100
6/25/2018	ACCEPTED	12	789	150
1/29/2019	DECLINED	13	122	125
2/2/2019	DECLINED	14	122	105
7/10/2020	DECLINED	15	445	90
8/13/2020	ACCEPTED	16	445	100
9/3/2020	DECLINED	17	445	150

The result should look something like this

DATE	ACCEPTED/DECLINED	ESTIMATE NUMBER	Car Number	AMOUNT $
1/2/2018	ACCEPTED	11	445	100
6/25/2018	ACCEPTED	12	789	150
2/2/2019	DECLINED	14	122	105
9/3/2020	ACCEPTED	16	445	100

Thanks for the help. Feel free to ask clarifying questions if needed.

[stevedep · Accepted solution]

Hi,

This was a nice one! Please see below how it works. You can select a date, which is used to define the time frame of 6 months. It will then look for accepted quotes, if it does not find any (coalece), it will take the most recent declined quote. Enjoy!
Please note; the date table is unrelated!

The DAX code:

_Filter =
VAR _selCurrentDate =
    SELECTEDVALUE ( DateDim[Date] )
VAR _6monthprior =
    EDATE ( _selCurrentDate, -6 )
RETURN
    IF (
        NOT ( ISBLANK ( SUM ( 'Table'[AMOUNT $] ) ) ),
        COALESCE (
            CALCULATE (
                COUNT ( 'Table'[ACCEPTED/DECLINED] ),
                FILTER (
                    'Table',
                    [DATE].[Date] > _6monthprior
                        && [DATE].[Date] <= _selCurrentDate
                        && [ACCEPTED/DECLINED] = "ACCEPTED"
                )
            ),
            VAR _SELcar =
                SELECTEDVALUE ( 'Table'[Car Number] )
            VAR _MaxDateDeclined =
                CALCULATE (
                    MAX ( 'Table'[DATE].[Date] ),
                    FILTER (
                        ALL ( 'Table' ),
                        [Car Number] = _SELcar
                            && [DATE].[Date] > _6monthprior
                            && [DATE].[Date] <= _selCurrentDate
                            && [ACCEPTED/DECLINED] = "DECLINED"
                    )
                )
            RETURN
                CALCULATE (
                    COUNT ( 'Table'[ACCEPTED/DECLINED] ),
                    FILTER ( 'Table', [DATE].[Date] = _MaxDateDeclined && [Car Number] = _SELcar )
                )
        ),
        BLANK ()
    )

File is attached. 

Kind regards, Steve. 

 

[Big_Trucks]

@stevedepThank you so much for the help. You are directionally where I need to be headed. I think slight tweaks will to your outline will work for me. I hadn’t even thought to break it down with date VARs. That really helped to go line by line as needed. I know it was a bit of a complex problem, but you nailed it. Thanks again.

[stevedep]

Welcome! I enjoy the complex problems the most 🙂

[lbendlin]

Your criteria are ambiguous for car 445.  Do you want to see all accepted quotes or only the latest one?

"The end goal really is to only have one estimate for each car number in a 6 month timeframe." 

how do you define the boundaries of that 6 month timeframe?