Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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!

 

 

 

Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    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/EX3wSnvP07pDsy23uytqaacBiodIfW_1xk6D0gEkXFso4Q?e=QXywFu

     

    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

16 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    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/EX3wSnvP07pDsy23uytqaacBiodIfW_1xk6D0gEkXFso4Q?e=QXywFu

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.

       

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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!

      • PhilipTreacy's avatar
        PhilipTreacy
        Super User
        5 years ago

        Hi Anonymous 

        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

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous , 

     

    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

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    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
)

     

     

     

    For more details,please refer to the pbix file https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EScmwt8sUKpCuXIlzqD4M4YBXba_mt3H3cI2vByWWhxFtQ?e=mhLhfh

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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!