cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nguyen
Helper III
Helper III

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

Nguyen_0-1620205611074.png

Nguyen_1-1620205629441.png

 

Thanks much for your support!

 

Data:

ProductWeekValue
A16.202120
A17.202130
B16.202131
B17.202119
A14.202112
A15.202110
B14.202117
B15.202131
1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

Hi @Nguyen ,

 

Please follow these steps:

1. Add a nw table for slicer:

table 2 for slicer.jpg

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:

dynamic output.gif

 

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.

View solution in original post

3 REPLIES 3
Eyelyn9
Community Support
Community Support

Hi @Nguyen ,

 

Please follow these steps:

1. Add a nw table for slicer:

table 2 for slicer.jpg

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:

dynamic output.gif

 

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.

View solution in original post

amitchandak
Super User IV
Super User IV

@Nguyen , 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))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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?

Nguyen_0-1620210189198.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors