Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Guys, I'm working on a table with last year two period and this year two period datas.
I want to create a slicer using column[ Products] to see this session's Region Level 2 and last session's Region Level 1 comparison, which means I have two conditions to consider.
So I have five colomuns to consider, and also other columns like SALES, comments and others to show in the table visual.
Products | Region | Region Level | Session | Index | Sales | Comments |
Product A | Headquarter | 1 | 2022 Annual | 1 | ||
Product A | West | 2 | 2022 Annual | 1 | ||
Product B | East | 2 | 2022 Annual | 1 | ||
Product C | Headquarter | 1 | 2022 Annual | 1 | ||
Product A | Headquarter | 1 | 2023 Interim | 2 | ||
Product B | West | 2 | 2023 Interim | 2 | ||
Product C | West | 2 | 2023 Interim | 2 | ||
Product A | West | 2 | 2023 Annual | 3 | ||
Product B | Headquarter | 1 | 2023 Annual | 3 | ||
Product C | Headquarter | 1 | 2023 Annual | 3 |
picture
Because I have more than twenty products in the database.
Can it work when I choose Product A it would show the value of both this session's Level 2 and Last session's level 1 data?
I added a column index for 2022 Annual as 1, 2023 Interim as 2, 2023 Annual as 3. would be more index in the future.
I create a measure Using following:
So is it possible to create a dynamic measure to use in the slicer filter?
Thank you all.
Solved! Go to Solution.
Hi @Anonymous ,
Directly filter with a slicer to obtain the following
If the above method is not what you want, you want to compare on the same line, such as comparing the sales of level 1 and level 2 under the same session. Then your index needs to be created as follows:
Group by Session column and add a All Rows column.
Add a custom column to add index and then expand it.
Create a measure to get level 1 sales.
level 1 sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),[Session]=MAX('Table'[Session])&&[Region Level]=MAX('Table'[Region Level])-1))
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Directly filter with a slicer to obtain the following
If the above method is not what you want, you want to compare on the same line, such as comparing the sales of level 1 and level 2 under the same session. Then your index needs to be created as follows:
Group by Session column and add a All Rows column.
Add a custom column to add index and then expand it.
Create a measure to get level 1 sales.
level 1 sales = CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),[Session]=MAX('Table'[Session])&&[Region Level]=MAX('Table'[Region Level])-1))
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
9 | |
7 |