How to show not filtered data

0
votes

I am new to power bi, and this question might be a common question and already answered, but I didn't find any precise solution.

I have two tables; "Dates" and "Accounts".

  1. Dates table has only one column: "Date"(date type). It has only date value, which is day based.
  2. Accounts table has two columns; Name(text type) and CreatedDate(date dypttype).

In my power bi model, there is a relationship(many to one, single, active) between Dates.Date and Accounts.CreatedDate columns.

I want to show account names that except the filtered ones. For example, my Accounts table look like this:

Name CreatedDate
A Company 2020-01-01
B Company 2020-12-15
C Company 2021-03-03
D Company 2019-05-27

I have a slicer and use Dates.Date as It's field.

Slicer With Date Field

When I filtered my data as last 1 year (2020-08-18 - 2021-08-18), I want to show the data which is not filtered. I want to see this:

Name CreatedDate
A Company 2020-01-01
D Company 2019-05-27

How to show the data which is not filtered?

1
powerbidax
I really hope this is not possible. If you are new to Power BI, stick to it's standard behaviour and don't confuse yourself and others. – Peter
I didn't like this either, but they literally asked me to do this whatsoever. – Madao

1 Answers

1
votes

You can create a filter Measure like following

    _filter:= 
SWITCH (
    TRUE (),
    /* t1 = what is value selected ?*/
    CONVERT ( SELECTEDVALUE ( 'Calendar'[Calendar_Date] ), INTEGER ) = BLANK ()   
    /* t2=what is the max CreatedDate value visble in the filter context*/
    /*if t1 and t2 both blank then return -999 else return t1-t2 which should be 0*/
        && CONVERT ( MAX ( tbl[CreatedDate] ), INTEGER ) = BLANK (), -999, 
    CONVERT ( SELECTEDVALUE ( 'Calendar'[Calendar_Date] ), INTEGER )
        - CONVERT ( MAX ( tbl[CreatedDate] ), INTEGER )
)

SOlution