dynamic filtering of table visual data based on different dependent conditions

[aatish178]

Hi All, I am new to power bi and need your help with below scenario:

I have below data in my table visual:

article no	region	retailor no	valid date	qty
10	East IND	10001	10/10/2024	500
10	East IND	10002	10/10/2024	500
10	West IND	11001	10/10/2024	400
11	South IND	22211	12/12/2024	300
11	South IND	22212	12/12/2024	100
11	East IND	10001	10/10/2024	600

I want to filter this table visual to show only required row with latest retailor no with below 3 scenario:

In order to show who is the latest retailor among above data, the check 1 is :

Check 1: for each article no, region UNIQUE COMBINATION, find out what is the latest(max) valid date., if found then show that row as a result, if not found then:

Check 2: from the result(filtered data) of Check 1, try to find out what is the latest(max) qty., if found then show that row as result., if not then:

Check 3: from the result(filtered data) of Check 2, try to find out what is the latest(max) retailor no, if found show it as a final result.

Let me explain this with example., article no=10, region= East IND

for above article no and region unique combination we have 2 rows, hence first check what is the max of valid date i.e., 10/10/2024.

this date is same for 2 retailors hence next check what is the max of qty., here it is 500 which is again same for 2 rows, hence finally check what is the max of retailor no., here it is 10002., hence show that row alone as a final result.

article no	region	retailor no	valid date	qty
10	East IND	10002	10/10/2024	500

The overall final result from these 3 scenarios would be as shown below:

article no	region	retailor no	valid date	qty
10	East IND	10002	10/10/2024	500
10	West IND	11001	10/10/2024	400
11	South IND	22211	12/12/2024	300
11	East IND	10001	10/10/2024	600

Below is the image FYR:

Can someone plz help

 @Ashish_Mathur  @quantumudit  @quantumudit1  @Anonymous   @gmsamborn @Anonymous 

Thanks in advance,

Aatish

[Anonymous]

Hi @aatish178 ,

Please try:

Create a calculated column to calculate the latest valid date for each unique combination.

Latest Valid Date = 
CALCULATE(
    MAX('Table'[valid date]),
    ALLEXCEPT('Table','Table'[article no],'Table'[region]))

Create a measure to find the maximum quantity.

Latest Qty = 
VAR CurrentArticle = SELECTEDVALUE('Table'[article no])
VAR CurrentRegion = SELECTEDVALUE('Table'[region])
VAR LatestDate = MAX('Table'[valid date])
RETURN
CALCULATE(
    MAX('Table'[qty]),
    FILTER(
        ALL('Table'),
        'Table'[article no] = CurrentArticle && 'Table'[region] = CurrentRegion && 'Table'[valid date] = LatestDate))

Create a measure to find the latest retailer ID.

Latest Retailor No = 
VAR CurrentArticle = SELECTEDVALUE('Table'[article no])
VAR CurrentRegion = SELECTEDVALUE('Table'[region])
VAR LatestDate = MAX('Table'[valid date])
VAR LatestQTY = [Latest Qty]
RETURN
CALCULATE(
    MAX('Table'[retailor no]),
    FILTER(
        ALL('Table'),
        'Table'[article no] = CurrentArticle && 'Table'[region] = CurrentRegion && 'Table'[valid date] = LatestDate && 'Table'[qty] = [Latest Qty]
        )
)

Create a measure that returns 1 if the set conditions are met, otherwise returns Blank.

Measure = 
VAR _isLtestDay =
    IF ( MAX ( 'Table'[valid date] ) = MAX ( 'Table'[Latest Valid Date] ), 1, BLANK() )
VAR _isMaxQTY =
    IF ( _isLtestDay = 1 && MAX ( 'Table'[qty] ) = 'Table'[Latest Qty], 1, BLANK() )
RETURN
    IF (
        _isMaxQTY = 1
            && MAX ( 'Table'[retailor no] ) = 'Table'[Latest Retailor No],
        1,
        BLANK()
    )

In the Filter at the visual object level, set the condition to Not Blank.

The final page effect is as shown below:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[aatish178]

Dear Yang, 

Many thanks for this workaround.

The solution is working as expected. However, I want to show one more small change in the table visual., as shown in below screenshot:

Here I have added one more table with field report interval date .,and used that field as a measure in Qty Calculated measure., my expectation was the final table visual should show valid dates which are less than report interval date., and I am able to achieve that., Now on the top of it I have added valid till date field., and it is showing date as 2025-10-10 and 2025-08-08., 

My Requirement is: VALID TILL DATE FIELD SHOULD SHOW BLANK FOR DATES WHICH ARE GREATER THAN REPORT INTERVAL DATE i.e., 2025-10-10 & 2025-08-08 SHOULD BE SHOWN AS BLANK AND THE DATE WHICH IS SMALLER THAN REPORT INTERVAL DATE SHOULD BE SHOWN AS IT IS., IN THIS CASE 2024-08-31 SHOULD BE SHOWN FOR REPORT INTERVAL END DATE. 

Can you please help me with this change.

I am unable to attach the updated pbix file here.,

Aatish