Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Visualize only the value match with requirement

Dear all,

May I ask your advice for below issue?

 

Data: product, week, value

Expectation:

  • flexibility to select 2 weeks by a slicer
  • The chart will only shows product that have value of the latest week higher than 28 and the furthest week is less than 28.

For example:

When I select week 16.2021 and week 17.2021

  • Result: display only value of product A in week 16.2021 and 17.2021.
  • Reason: the value of product A in week 17.2021 is 30 (>28) and value of product A in week 16.2021 is 20 (<28).

When I select week 14.2021 and week 16.2021

  • Result: display only value of product B in week 14.2021 and 16.2021
  • Reason: the value of product B in week 16.2021 is 31 (>28) and value of product B in week 14.2021 is 17 (<28).

The chart should show value of product match with requirement.

Example: when select week 14.2021 and 16.2021

 

Thanks much for your support!

 

Data:

ProductWeekValue
A16.202120
A17.202130
B16.202131
B17.202119
A14.202112
A15.202110
B14.202117
B15.202131
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Please follow these steps:

    1. Add a nw table for slicer:

    2. Add measures :

    <28 = 
var _min=MIN('Table 2'[Week])
var _minvalue=CALCULATE(MAX('Table'[Value]),FILTER('Table',[Product]=MAX('Table'[Product])&&[Week]=_min))
return IF(_minvalue<28,_minvalue)
    >28 = 
var _max=MAX('Table 2'[Week])
var _maxvalue=CALCULATE(MAX('Table'[Value]),FILTER('Table',[Product]=MAX('Table'[Product])&&[Week]=_max))
return IF(_maxvalue>28,_maxvalue)
    flag = IF(ISBLANK([<28]),[>28],[<28])
    output = IF(CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[Product]),[flag]<>BLANK()))=2,[flag])

    The final output is shown below:

     

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a measure like

     

    measure =
    var _1 = calculate(distinctCOUNT([Week]), allselected(Table)
    var _2 = countx(allselected(Table) , [product] =max([product]),[Week])
    return
    calculate(sum(table[Value]), filter(Table, _1=_2))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

      I try the code but below issue.

      Besides, I need to compare value of the latest week >28 and the furthest week < 28. But this measure not yet add in this part. Could you please advise?

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Please follow these steps:

    1. Add a nw table for slicer:

    2. Add measures :

    <28 = 
var _min=MIN('Table 2'[Week])
var _minvalue=CALCULATE(MAX('Table'[Value]),FILTER('Table',[Product]=MAX('Table'[Product])&&[Week]=_min))
return IF(_minvalue<28,_minvalue)
    >28 = 
var _max=MAX('Table 2'[Week])
var _maxvalue=CALCULATE(MAX('Table'[Value]),FILTER('Table',[Product]=MAX('Table'[Product])&&[Week]=_max))
return IF(_maxvalue>28,_maxvalue)
    flag = IF(ISBLANK([<28]),[>28],[<28])
    output = IF(CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[Product]),[flag]<>BLANK()))=2,[flag])

    The final output is shown below:

     

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