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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate days past term need dropdown slicer

Hi,

 

I want to calculate something like this:

0 days past terms will be always today's day.

 

I want a dropdown slicer with values starting from 0 to 10.

where

 

if days past terms >=1, then return me 0

( so when I click on 0, i should get all the dates which are less then and equal to 1/17/2021)

if days past terms >=2, then return me 1

( so when I click on 1, i should get all the dates which are less then and equal to 1/16/2021)

if days past terms >=3, then return me 2

( so when I click on 2, i should get all the dates which are less then and equal to 1/15/2021)

if days past terms >=4, then return me 3

( and so on...)

if days past terms >=5, then return me 4

if days past terms >=6, then return me 5

if days past terms >=7, then return me 6

if days past terms >=8, then return me 7

if days past terms >=9, then return me 8

if days past terms >=10, then return me 9
if days past terms >=11, then return me 10

else blank

 

any help.. My conditions fails when I do this may be I am not doing it correctly.

 

attached the sample file:

https://www.dropbox.com/s/grxqnjtmyczhbdl/Sample%20file.pbix?dl=0

 

 

@amitchandak  @Ashish_Mathur 

2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

v-shex-msft
Community Support
Community Support

HI @Anonymous,

You can't directly use a dropdown slicer with raw table fields to achieve this effect, I'd like to suggest you refer to the following steps:

1. Create a calculated column to replace day diffs based on conditions.

Replaced =
IF (
    [Days past terms2] > 0,
    IF (
        [Days past terms2] <= 3,
        [Days past terms2] - 1,
        MIN ( [Days past terms2] + 1, 11 )
    )
)

2. Create a parameter table from 0 to 10 and use its value to create a slicer.

Create and use what-if parameters to visualize variables in Power BI Desktop 

3. Write a measure to compare raw table value and parameter table selection to return the tag. Then use it on the raw visual 'visual level filter' to filter records based on slicer selections.

Measure =
IF ( MAX ( Sheet1[Replaced] ) >= MAX ( Parameter[Parameter] ), 1, 0 )

Notice: I attached the sample pbix file below.

Regards,

Xiaoxin Shen

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @Anonymous,

You can't directly use a dropdown slicer with raw table fields to achieve this effect, I'd like to suggest you refer to the following steps:

1. Create a calculated column to replace day diffs based on conditions.

Replaced =
IF (
    [Days past terms2] > 0,
    IF (
        [Days past terms2] <= 3,
        [Days past terms2] - 1,
        MIN ( [Days past terms2] + 1, 11 )
    )
)

2. Create a parameter table from 0 to 10 and use its value to create a slicer.

Create and use what-if parameters to visualize variables in Power BI Desktop 

3. Write a measure to compare raw table value and parameter table selection to return the tag. Then use it on the raw visual 'visual level filter' to filter records based on slicer selections.

Measure =
IF ( MAX ( Sheet1[Replaced] ) >= MAX ( Parameter[Parameter] ), 1, 0 )

Notice: I attached the sample pbix file below.

Regards,

Xiaoxin Shen

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@Anonymous , I think you have put condition in reverse order. See if this example of a new column can help

 

Column = SWitch(True() ,
[Days past terms2] >=11,10,
[Days past terms2] >=10,9,
[Days past terms2] >=9,8,
[Days past terms2] >=8,7,
[Days past terms2] >=7,6,
[Days past terms2] >=6,5,
[Days past terms2] >=5,4,
[Days past terms2] >=4,3,
[Days past terms2] >=3,2,
[Days past terms2] >=2,1,
[Days past terms2] >=1,0,
blank()
)

Anonymous
Not applicable

Hi @amitchandak ,

Thnaks for your replY

 

 

I had used Switch but the problem I get is when I select 9 ,8,7 etc..  in the dropdown, It brings me only one date. I want all the date less than and = 1/11/2021 for the example attached.

 

How would I get that?

 

tejaswidmello_0-1611069596045.png

 

@az38  @TomMartens 

 

Thanks,

Tejaswi 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.