Visualize only the value match with requirement

[Anonymous]

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:

Product	Week	Value
A	16.2021	20
A	17.2021	30
B	16.2021	31
B	17.2021	19
A	14.2021	12
A	15.2021	10
B	14.2021	17
B	15.2021	31

[v-eqin-msft · Accepted solution]

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.

[amitchandak]

@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]

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?