Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
The third column is a measure that computes a DateDiff for 2021.
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:
But...this just doesn't work...
Is there any way to do this sort of thing?
Thanks!
Solved! Go to Solution.
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
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
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.
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
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!
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
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 @Anonymous ,
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
Try this link:
https://drive.google.com/file/d/13DB-11PChbX1PAo-KYg1VRK8K47YZVnS/view?usp=sharing
Thanks!
Hi @Anonymous
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)
)
Regards
Phil
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 @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
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
@Anonymous
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
Proud to be a Super User!
Here is link to refreshed demo file:
https://drive.google.com/file/d/1X_wiSU9R1Zu40U3etbs3t5KshEstYqEt/view?usp=sharing
Thanks!
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
@Anonymous 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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |