Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I need your help to create a dynamic measure which can be switched in a table automaticly by the user year selection.
Here is the details: I have 2 sorce tables which have the same column "Products" and a column "Year" and for each table there is 1 measure column SalesTable1 and 1 measure column SalesTable2.
So on the page I want to display a slicer Year selection and a simple table with Product as a dimension and Sales like a measure but here is the conditions:
If Today we are in the year 2023 so If the user chose 2022,2021,2020 etc I need to display SalesTable1 and If the user chose the current year (2023) I need to display SalesTable2
The next year if today we are 01/05/2024
and the Year chosen in the slicer is .....2021or 2022 or 2023 - I have to display SalesTable1 and if the year chosen is 2024 - SalesTable2
But if today we are in the range between 01/01/2024 and 30/04/2024
For the year till 2022 - SalesTable1 and for 2023 and 2024 - SalesTable2
Thanks a lot for your help Hope it is clear
@Anne14 , use either if statement or switch statement in a new measure where if the year matches year(today()),i.e. 2023 it gives salesTable2 else salesTable1
.
ok thanks but there is the condition about what month we are... il it is before mai or after that...
Can you give me an example for the full expression formula please ?
Thanks
@Anne14 Add the condition where if it's not this year it checks whether it is year(today()) - 1 and month(today()) < 5.
Something like this:
Hello,
Thanks a lot for your answer. I used the If function instead of switch and it is ok but I have two types of pages. One page where I have a year slicer so I can select 2021 or 2022 or 2023 etc and on the table I have the product and Sales: here all works fine but I have another page where I have a pivot table with product as line and year as column (always sales as measure).
But when I try to make this table I have error message (as you mentionned that This will return error if nothing is selected) that columns contain multiples where 1 was expected which is normal because I try to select 2 or more years and the IF is waiting for one... How I can make another formula with the same function but who is showing the result for the 2 Salestable1 and Salestable2 in the same table
Here is the result I expect
Year
2021 2022 2023
Product 1 Salestable1 Salestable1 Salestable2
Product2 Salestable1 Salestable1 Salestable2
thanks again
IF( values('table1'[Year]) = year(today()) && MONTH(today()) > 5, [Salestable1], [Salestable2])
This will give SalesTable1 for october 2023.
Use an isfiltered function for [year], also specify what is the required value when two years are selected.
hello
Thanks for your reply.
Let say we are in onctober 2023 and that 2021,2022,2023 are selected The required values are Salestable1 in the column 2023 and Salestable2 in the column 2021 and 2022.
(If we are in March 2023 in all the 3 columns I have to have Salestable2 )
Can you suggest me a formula?
Thanks
@Anne14 , This measure should work but introduce a date column somehow, because for multiple selection on slicer there needs to be a distinction. If the table visual just has productName and measure, then multiple selection is going to create a problem. In matrix visual, set rows as product, columns as year and values as measures.
that is what I did : try the matrix visual but I have the error that columns contain multiples where 1 was expected when I select 2 or more years in the slicer 😞 I think the IF function will always require one selected value.. I need some other solution
thanks
if year is part of column fields, there would be no need of slicer. If slicer is needed, make a field parameter.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
20 | |
19 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
24 | |
23 | |
21 |