Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All
First of all I have to apologize if I am asking a question which might be stupid, but I am fairly new and didn't find a solution for my problem by searching since a few days.
I have 2 tables, one giving me some periods, the other the numbers from 1 to 12.
All_Periods:
Year_Month | Index |
2017-01 | 1 |
2017-02 | 2 |
2017-03 | 3 |
2017-04 | 4 |
2017-05 | 5 |
2017-06 | 6 |
2017-07 | 7 |
2017-08 | 8 |
2017-09 | 9 |
2017-10 | 10 |
2017-11 | 11 |
2017-12 | 12 |
2018-01 | 13 |
2018-02 | 14 |
2018-03 | 15 |
2018-04 | 16 |
2018-05 | 17 |
2018-06 | 18 |
2018-07 | 19 |
2018-08 | 20 |
2018-09 | 21 |
2018-10 | 22 |
2018-11 | 23 |
2018-12 | 24 |
2019-01 | 25 |
2019-02 | 26 |
2019-03 | 27 |
2019-04 | 28 |
2019-05 | 29 |
NoOfPeriods:
NoOfPeriods |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
I have for each table a slicer, letting me select a period and a number of periods I want to look backwards (incl. the selected period).
I created a measure to get the slicer selection for the period:
SelectedPeriodEnd = SELECTEDVALUE(All_Periods[Year_Month];"Select a Value")
The measure to get the index of the selected period from column "Index":
IndexSelected = LOOKUPVALUE(All_Periods[Index];All_Periods[Year_Month];All_Periods[SelectedPeriodEnd])
Then I am defining the count of Periods available backwards basing on the selected period, which shall not exeed 12:
MaxPeriod = if(
if(HASONEVALUE(All_Periods[Year_Month]);
LOOKUPVALUE(All_Periods[Index];All_Periods[Year_Month];All_Periods[SelectedPeriodEnd]);
MAX(All_Periods[Year_Month]))>12;
12;
if(HASONEVALUE(All_Periods[Year_Month]);
LOOKUPVALUE(All_Periods[Index];All_Periods[Year_Month];All_Periods[SelectedPeriodEnd]);
MAX(All_Periods[Year_Month])))
For the value from the second slicer I created a measure, ensuring the selected number of periods does not exeed the MaxPeriod:
Anz.Period = if(SELECTEDVALUE(NoOfPeriods[NoOfPeriods];"Select a Number")<=All_Periods[MaxPeriod];
SELECTEDVALUE(NoOfPeriods[NoOfPeriods];"Select a Number");
if(SELECTEDVALUE(All_Periods[Year_Month];"Select a Value")>All_Periods[MaxPeriod];All_Periods[MaxPeriod];SELECTEDVALUE(All_Periods[Year_Month];"Select a Value")))
Now I am able to define the StartPeriod:
StartPeriod = LOOKUPVALUE(All_Periods[Year_Month];All_Periods[Index];All_Periods[IndexSelected]-(All_Periods[Anz.Period]-1))
Choosing period 2018-06 and number of periods 8 returns me following values:
SelectedPeriodEnd: 2018-06
StartPeriod: 2017-11
With these values I would like to create a visual table like:
SubTable A = CALCULATETABLE(All_Periods;FILTER(All_Periods; All_Periods[Year_Month]<="2018-06");FILTER(All_Periods; All_Periods[Year_Month]>="2017-11"))
but I like to use my measures "SelectedPeriodEnd" and "StartPeriod" instead of static values, in order to make it dynamic.
When I try
SubTable A = CALCULATETABLE(All_Periods;FILTER(All_Periods; All_Periods[Year_Month]<=[SelectedPeriodEnd]);FILTER(All_Periods; All_Periods[Year_Month]>=[StartPeriod]))
I always get the full range of periods in the table.
What am I doing wrong here ?
Best regards
Joerg
@Joerg ,
Based on your description, there're two many filtres apply on table 'ALL_Periods' like the slicer and filter statement in calculate table. So in general, I would suggest you to create an additional date table with the date column then create the slicer based on the date column, do not make relationship between the new date table and 'ALL_Periods'.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Dear Jimmy
Even with a simple model my attempt does not work. I can't use the measure 'Slicervalue' to filter the table.
I have a table with Fruits and Values, a second table with Slicervalues from 1 to 5.
Setting up a calculated filtered table does not work with the value from the Slicervalue.
Can you broaden my horizons ?
Cheers
Joerg
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |