cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
p6sk
Helper I
Helper I

Dynamic Slicer based on measure values

I need to build a simple table to summarize data by regions

and compare them by Year.  The goal is to have a single slicer

that updates two columns.

 

The first column is region name (easy enough, right?).

 

The second column is a measure that computes a DATEDIFF for the year 2020:

2020 Goal =
if(
  YEAR(CREATEDDATE) = 2020,
  DATEDIFF(Date1, Date2, DAY)
)

 

The third column is a measure that computes a DateDiff for 2021.

Running Total 2021 =
if(
  YEAR(EVM_VEGETATIONPOINT_MASTER[CREATED]) = 2021,
  DATEDIFF(Date1, Date2, DAY)
)
 
So far so good -- this all works fine.
But clearly the dates embedded in the measures are static.

 

So, I created slicer named "Year Created" with the YEAR values from my table,

and was trying to create dynamics by using the [Year Created] slicer as follows:

 

2020 Goal =
if(
  YEAR(CREATEDDATE) = [Year Created],
  DATEDIFF(Date1, Date2, DAY)
)
 
Running Total 2021 =
if(
  YEAR(EVM_VEGETATIONPOINT_MASTER[CREATED]) = [Year Created] + 1,
  DATEDIFF(Date1, Date2, DAY)
)

 

But...this just doesn't work...

Is there any way to do this sort of thing?

 

Thanks!

 

 

 

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @p6sk ,

 

First of first, the slicer will not have effect on calculated column, you can use measure only. Please try the following measure:

 

Current Year1 = SUMX(data,if(
    NOT(ISBLANK(data[start_date])) && 
    NOT(ISBLANK(data[end_date])) &&
    YEAR(data[CREATED]) = SELECTEDVALUE('Table'[Year]),  
    DATEDIFF(data[start_date], data[end_date], DAY)
))+0

Previous Year1 = SUMX('data',if(
    NOT(ISBLANK(data[start_date])) && 
    NOT(ISBLANK(data[end_date])) &&
    YEAR(data[CREATED]) = MAX('Table'[Year])-1, 
    DATEDIFF(data[start_date], data[end_date], DAY)
))+0

 

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EX3wSnvP07pDsy23uy...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

16 REPLIES 16
v-deddai1-msft
Community Support
Community Support

Hi @p6sk ,

 

First of first, the slicer will not have effect on calculated column, you can use measure only. Please try the following measure:

 

Current Year1 = SUMX(data,if(
    NOT(ISBLANK(data[start_date])) && 
    NOT(ISBLANK(data[end_date])) &&
    YEAR(data[CREATED]) = SELECTEDVALUE('Table'[Year]),  
    DATEDIFF(data[start_date], data[end_date], DAY)
))+0

Previous Year1 = SUMX('data',if(
    NOT(ISBLANK(data[start_date])) && 
    NOT(ISBLANK(data[end_date])) &&
    YEAR(data[CREATED]) = MAX('Table'[Year])-1, 
    DATEDIFF(data[start_date], data[end_date], DAY)
))+0

 

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EX3wSnvP07pDsy23uy...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

Dedmon -- thank you...this works perfectly!!

 

I can see how the selectedvalue function works in measure: "Current Year",

can you explain what the MAX function is doing in measure:  "Previous Year"?

 

Given the contents of our date (2019, 2020, 2021), I don't understand

why   MAX('YearTable'[Year])-1   is not always equal to 2020.

 

 

 

v-deddai1-msft
Community Support
Community Support

Hi @p6sk ,

 

First of first,you need to use measure instead of calculated column.There are too many start_date and end_date in one region,I don't know what aggregation you need,so I use max for example(you need to create a new table with year column for slicer):

 

2020 GoalX1 = if( YEAR(MAX(data[CREATED])) = SELECTEDVALUE('Table'[Year], 2020), DATEDIFF(MAX(data[start_date]), MAX(data[end_date]), DAY) ) 

previous_year1 = 
if(
    NOT(ISBLANK(MAX(data[start_date]))) && 
    NOT(ISBLANK(MAX(data[end_date]))) &&
    YEAR(MAX(data[CREATED])) = SELECTEDVALUE('Table'[Year],2020),
    DATEDIFF(MAX(data[start_date]), MAX(data[end_date]), DAY) * .9
)

 

1.gif

 

 

For more details,please refer to the pbix file https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EScmwt8sUKpCuXIlzq...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Hello Dedmon, thanks for the .pbix file...it looks like a step in the right direction, maybe we're nearly there...basically:

When a user selects 2019 on the slicer, they should see columns:  Region, 2019 data, 2020 data.

When a user selects 2020 on the slicer, they should see columns:  Region, 2020 data, 2021 data.

 

The original file contained remnants of my hacking/experiements, for clarity I have stripped the .pbix file down to the bare data, one slicer, and two simple measures.  Each measure contains a hard coded date along with a comment which ties back the behavior described above.  The dream is to remove the hard coded dates and instead use values from the slicer.

 

Here is a link to the refreshed .pbix file:   

https://drive.google.com/file/d/1X_wiSU9R1Zu40U3etbs3t5KshEstYqEt/view?usp=sharing

 

Thanks!

 

 

 

 

 

 

 

 

 

v-deddai1-msft
Community Support
Community Support

Hi @p6sk , 

 

I suggest you create a new table with year column as slicer. Then you can use the following meausre:

2020 Goal =
if(
  YEAR(CREATEDDATE) = SELECTEDVALUE('Table'[Year],2020),
  DATEDIFF(Date1, Date2, DAY)
)

Running Total 2021 =
if(
  YEAR(EVM_VEGETATIONPOINT_MASTER[CREATED]) = SELECTEDVALUE('Table'[Year],2020) + 1
  DATEDIFF(Date1, Date2, DAY)
)

 

You can upload your pbix file by onedrive for business and then share the link with us.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Hello Dedmon -- thanks for the note, let's see if you can access the .pbix file:

https://pge-my.sharepoint.com/:u:/p/p6sk/EZvEdTvTLJNOshTV4l8i85AB0oHuQMpSycsS7dtlLZ-ndA?e=hRJf4N

Hi @p6sk ,

 

I can't access to the pbix file by your link. Would you please check the permission for it ?

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

PhilipTreacy
Super User III
Super User III

Hi @p6sk 

Download this PBIX file with working example shown below

Use SELECTEDVALUE to get the selection from the slicer.  Something like

 

2020 Goal =
if(
  YEAR(CREATEDDATE) = SELECTEDVALUE('Table'[Year]),
  DATEDIFF(Date1, Date2, DAY)
)

 

selyr.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thanks, Philip -- this looks promising...unfortunatley I'm a complete newb and need a bit more detail...

 

It looks like using SELECTEDVALUE disconnects the measure from the slicer.

Also, using SELECTEDVALUE, how would I add the extra year to the measure named "Running Total 2021"?

 

Generically, I need to a select YEAR from a slicer which would:

1. put data from YEAR in the Goal column

2. put data from YEAR + 1 in the Running Total column

 

Thanks!

Hi @p6sk 

No worries. Did you open my PBIX file and check it?

The way it works is that SELECTEDVALUE checks what Year is selected/filtered in the data table.  The way that PBI works is that everything is connected. You don't need to explicitly check the slicer to see what is selected in it.  You check the underlying column in the data table to see what is selected or in other words you check the column to see what filter is applied.

So using the slicer you can select a year, this results in the data in the Table visual only showing the data for that year.

In my example image above I've selected 2021, so only data for 2021 is shown in the visual.

SELECTEDVALUE checks the 'Table'[Year] column to see what filter (slicer selection) is applied.

You can see in the card visual Selected Year that my measure shows the year selected in the slicer.  This measure is just 

SELECTEDVALUE('Table'[Year])

 

So looking at your measures, all you need to do is substitute your hard coded year values for  SELECTEDVALUE('Table'[Year])  and this will pick up the year selected in your slicer.

Your measures would look like

2020 Goal =
if(
  YEAR(CREATEDDATE) = SELECTEDVALUE('Table'[Year]),
  DATEDIFF(Date1, Date2, DAY)
)
Running Total 2021 =
if(
  YEAR(EVM_VEGETATIONPOINT_MASTER[CREATED]) = SELECTEDVALUE('Table'[Year]) + 1
  DATEDIFF(Date1, Date2, DAY)
)

 

There are a couple of things to note.

1. I'm using 'Table'[Year] to indicate the Table name and the Year column.  You'll need to change these to match your table and year column names.

2. As you are trying to make this dynamic I assume you will be looking to change the names of your measures as they won't be fixed for 2020 Goal and Running Total 2021  

 

Hope that clears things up?  Try implementing the code and if it's not working please post back and include your PBIX file.

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Phil – thanks again for your help on this.

I’ve been toying with SELECTEDVALUE but still can’t make it work.

 

How can I upload a pbix file here?

 

--Pete

@p6sk 

Hi Pete,

Sorry I missed your responses.  Yuo need to @ mention me otherwise I don't get a notificationthat you've replied.

Type @ and then choose my name when replying.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi Phil -- thanks for the note/advice...I'm new with this system but learning fast.

You should be able to download my demo file:

https://drive.google.com/file/d/13DB-11PChbX1PAo-KYg1VRK8K47YZVnS/view?usp=sharing

parry2k
Super User III
Super User III

@p6sk Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.