cancel
Showing results for
Did you mean:
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_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

2 REPLIES 2
Community Support

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.

Frequent Visitor

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

Announcements

#### The Power BI Community Show

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

#### Charticulator Design Challenge

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

#### Check it Out!

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