disable filtering relationship

[JohannTTG]

The situation:
- a table with years and the user sees a slicer with a single selection.
- a fact table also with a year.
- a relationship between the years.

Because of the single selection the user only sees one year in a table visual.
All this is really simple.

Now the requirement:
A second visual for the some fact table.
The user should not only see the selected year but 3 years before.
But there is still a single-selection-slicer for the year!

My solution:
I created two measures: maxYear and minYear (based on the single selection value). 
I imported the fact table a second time – no relationship to the year-table for this copy.
Then I created a measure like this:

ValuesInYearRange2 = CALCULATE( SUM(FactTab2[Value2]) ;
                                                          FILTER( FactTab2 ; FactTab2[Year] >= [MinYear] && FactTab2[Year] <= [MaxYear]) )

This works as expected.

But I think there should be a better way to reach my goal without the table-copy. The only thing I need is to disable the relationship between year-table and fact-table when the measure is calculated. For this, I failed to far. Any help is appreciated!

Thanks, jomu

[Icey]

Hi @JohannTTG ,

Please check if this post is helpful: Display Last N Months & Selected Month using Single Date Dimension in Power BI.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[AllisonKennedy]

You can either build aproper date table and use datesinperiod or use @BA_Pete measure but with

All (facttab [YEAR ] )

Instead of all (facttab)

instead of clearing ask the filters on fact table this will clear only the filter on the year column and still keep filters for products, category, region, etc. 

Sorry for the messy reply, I am answering from my phone. 

[BA_Pete]

Hi @JohannTTG ,

Try this instead. I've assumed your original fact table is just called FactTab, and I've added ALL into your FILTER table to ignore the slicer filter being applied.

ValuesInYearRange2 = 
CALCULATE(
    SUM(FactTab[Value]);
    FILTER(
        ALL(FactTab);
        FactTab[Year] >= [MinYear]
        && FactTab[Year] <= [MaxYear]
    )
)

Pete

[JohannTTG]

Sorry Pete,

this is not the solution. It sums up all values (!) and puts the same value in any cell. But there is still only a single cell visisble (that of the selected year). 

[vanessafvg]

is it possible to see the structure of your data? in theory you should be able to create another relationship on the same table, it is inactive, and then use the userelationship() function to activate the relationship for your selection but need to see your current model.

[JohannTTG]

I have an experimental environment.

Table DimTab

--> column year , an integer like 2000, 2001, .... 2020

--> MaxYear, MinYear, SingleYear, measures based on the selection. e.g. MaxYear = 2020, MinYear = 2017

Table FactTab

--> column Year --> exactly the same as in DimTab

--> column Value1 --> integer, 1, 2, 3, 4, ....

Table FactTab2 --> copy of FactTab with the (working) measure ValuesInYearRange2 

So what I want is:

YearsInRange should work like ValuesInYearRange2 (as if the relationship between DimTab and FactTab does not exists).

Currently I am not sure if I am on the right track ....

The measure seems to work but the visual only displays only the selected year but not the other years!

So maybe, it is not a DAX-problem but a visual problem.

[BA_Pete]

Hi @JohannTTG ,

Sorry, I need to dash off so can't revisit this for you.

I can see that @amitchandak , @vanessafvg , @PaulDBrown , and @AllisonKennedy  are all active right now, so I'm hoping one of them can pick up the @ and get this resolved for you.

Pete