Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors