cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Grogu69
Frequent Visitor

Filter with calculated number of days based on Slicer selection

Hi everyone,

 

I'm encoutering difficulties to achieve something simple I believe, but obviously not for me.. :(. Can you help me please ?

 

Let's say in my Table1 I have in my first column fruits name (Apple, Orange, etc...).  In column 3 I have the number of days since I got my first fruit for each line (I have many oranges, so I use "max" to get the number of days of each oldest fruit)

So in my example I got my first Orange 353 days ago, first apple 176 days etc...

Grogu69_0-1670324359849.png

 

I have also a segment to filter only One Fruit (or by fruit type), so my Table1 show only filtered lines if I tick a case

Grogu69_1-1670324494940.png

 

For my need, I created a Slicer based on a Calendar Table (linked to my Table1). It dynamically change values of my columns 2 and 4 (it's the attended behaviour) and the Column 3 stay identical, normal since it's a "max" value, who mustn't change

 

Now, for some reason, I need to add a Column "5" with a condition : 

If the number of Network Days selected in the slicer is smaller than my Column 3, it must show this value (named "EcartSlicer" below)

If not, it must show the "Column3" value

 

I created a measure to get the number of Network Days of the slicer, this part work wells : 

EcartSlicer = var _max = maxx(ALLSELECTED('Calendrier'), 'Calendrier'[Date])
var _min = minx(ALLSELECTED('Calendrier'),'Calendrier'[Date])
return
NETWORKDAYS(_min,_max,1,JoursFeries)
 
Then I created another measure with my IF statement :
Test = IF([EcartSlicer]<max('Table1'[Column3]),[EcartSlicer],max('Table1'[Column3]))
 
Now I got two issues : 
If I add my measure as Column 5 on Table1, it shows the same number of days for every line (it matches the [EcartSlicer]) value, no matter what the fruit is. So I guess I need to do something else but I don't find what...
Grogu69_2-1670324846803.png

 

 Second issue :
When I add this column, my filters don't work properly anymore : If I choose to show only Oranges by example, all the lines stay, but every value except the measure become blank. It should show only the "Orange" Line
 
Grogu69_3-1670324986887.png   Grogu69_4-1670326175479.png

 

I hope I was clear, if not tell me.

Thanks for your help

 
1 ACCEPTED SOLUTION

I finally could get the job done by playing with the filters, since columns 2 / 3 / 4 are empty only when I apply a filter on my segment, if I also apply a page filter to not show empty values, I get only the wished line.

It's a bypass but it's working

 

Thanks for your help anyway

View solution in original post

3 REPLIES 3
Mahesh0016
Solution Sage
Solution Sage

Mahesh0016_0-1670328399367.png

I think this solution is helpful for you.

Thanks for your answer, but I'm sorry I don't understand it, could you elaborate ?

 

For me your formula is exactly the same as mine 

"EcartSlicer = var _max = maxx(ALLSELECTED('Calendrier'), 'Calendrier'[Date])

var _min = minx(ALLSELECTED('Calendrier'),'Calendrier'[Date])
return
NETWORKDAYS(_min,_max,1,JoursFeries)"
 
But I see that your filter shows only the wished line, so there it seems that I'm doing something wrong....
My pbix has a lot of imbrications, I'll try to make a new one with only this need, to see if it's better or not

I finally could get the job done by playing with the filters, since columns 2 / 3 / 4 are empty only when I apply a filter on my segment, if I also apply a page filter to not show empty values, I get only the wished line.

It's a bypass but it's working

 

Thanks for your help anyway

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.