cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joerg
Frequent Visitor

Filter a table with single column by a value from measure

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_MonthIndex
2017-011
2017-022
2017-033
2017-044
2017-055
2017-066
2017-077
2017-088
2017-099
2017-1010
2017-1111
2017-1212
2018-0113
2018-0214
2018-0315
2018-0416
2018-0517
2018-0618
2018-0719
2018-0820
2018-0921
2018-1022
2018-1123
2018-1224
2019-0125
2019-0226
2019-0327
2019-0428
2019-0529

 

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

 

 

 

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@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

 

2019-05-15 07_59_11-Sample - Power BI Desktop.jpg

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors