Forum Discussion

aatish178's avatar
aatish178
Icon for Helper IV rankHelper IV
2 years ago

dynamic filtering of table visual data based on different dependent conditions

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

I have below data in my table visual:

article noregionretailor novalid dateqty
10East IND1000110/10/2024500
10East IND1000210/10/2024500
10West IND1100110/10/2024400
11South IND2221112/12/2024300
11South IND2221212/12/2024100
11East IND1000110/10/2024600

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 noregionretailor novalid dateqty
10East IND1000210/10/2024500


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

article noregionretailor novalid dateqty
10East IND1000210/10/2024500
10West IND1100110/10/2024400
11South IND2221112/12/2024300
11East IND1000110/10/2024600


Below is the image FYR:

 

Can someone plz help

 Ashish_Mathur  quantumudit  quantumudit1  Anonymous   gmsamborn Anonymous 

 

Thanks in advance,

Aatish

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      aatish178
      Icon for Helper IV rankHelper IV
      2 years ago

      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