Creation of PowerBI report with 2 visuals / "tables" - filter selection AND inverted selection

[Andrey_Hamburg]

Hello dear community,

start situation: I have data table (based on D365 F&O data), consisting of - basically - 2 columns:
|PrimaryKey | Date_of_event|

I use MS Fabric to consume D365 data. I created PowerBI report, which uses "date_of_event" column as date slicer of type "range", and one visual of type "table" showing PrimaryKeys, which belong to the choosen time slot (from-to range)

Now I have a rather tricky (in my opiniion) task: I have to add another visual of type "table" which shows INVERTION of the range - so, PrimaryKeys which are NOT in the range of slicer.

I spent already a couple of days looking for a proper soluton. Nearly all of them employ calculated tables, which are not supported by semantic model in MS Fabric. So, I changed to PowerBI desktop, imported my data from Onelake and tried to solve my task - unfortunatelly, without success.

I tried to use the ways described here: https://community.fabric.microsoft.com/t5/Desktop/Creating-an-inverse-filter/td-p/2086462

My issue is that using separate table for slicer filter (which is not connected to a main table) breaks my filtering for the first table (within choosen range).

Can anyone give a hint how to implement 2 visuals at the same time - with range selection and outside of the filter selection?

best regards from Hamburg,
Andrey

[v-saisrao-msft]

Hi @Andrey_Hamburg,

I hope you had a chance to review the solution shared @MFelix . If it addressed your question, Consider accepting the helpful reply as solution it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

Thank you.

[Andrey_Hamburg · Accepted solution]

Hello,

unfortunatelly, my issue is not resolved yet. Somehow I could reproduce the pattern proposed by @MFelix. So, I'm trying to understand a solution pattern for this task. 

[v-saisrao-msft]

Hi @Andrey_Hamburg,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @Andrey_Hamburg,
It's good to know you were able to replicate @MFelix  pattern. Let us know where you're encountering difficulties, and we can assist you in making further progress.

Thank you

[MFelix]

Hi @Andrey_Hamburg,

What was the part you were not able to reproduce? Do you want me to send you a PBIX file with the example?

[MFelix]

Hi @Andrey_Hamburg ,

This should be achievable without the need for additional tables. You refer that you have a date for filtering so I assume that you have a calendar with a relationship to the table try to create the following measure:

Not in Selection = 
var _Values = SELECTCOLUMNS('Table', "ID", 'Table'[ID])

Return
     IF( COUNTROWS(_Values) = BLANK(),1)

Has you can see on the table that includes the measure you get only the values that do not match.

I have tested this with a lakehouse with 70M rows and it works properly, but this will depend on the size of your data you may need to add filter to the calculation.

But this should work without the need to have new tables in your model.

Since this is an inversion you need to use the measure in the table visual then just hide the values on the table reducing the size of the column

[Andrey_Hamburg]

Unfornatelly, I could not repeat your example. I did the same - and in my case the "not_in_selection" measure as empty. 

Any idea what I did wrong in this case? My slicer is "attached" directly to the "Date_" column.

Best regards from Hamburg

[MFelix]

Hi @Andrey_Hamburg .

Since you are using a single table you won't be able to get the result you need.

When you use a column in a slicer you get the rows for those specific selection, and it does not matter how you slice and dice the information you will always get only those rows on the other visuals since you are not able to get the full value of the table since the slicer (same thing for the filter pane) will set the filter context and you cannot make it different from other.

You can however make a metric that will allow you to show those values in a list of values if you do a measure similar to this:

List of ID = 
var _selectitems = VALUES('Table'[ID])
var _TableFiltered =  FILTER(ALL('Table'), NOT('Table'[ID] in _selectitems))

Return
CONCATENATEX(_TableFiltered, 'Table'[ID], ",")

Has you can see in the image the card will show the values has a list:

If you want to have a different look you can always try to use a different syntax like:

//Line break for separation
List of ID = 
var _selectitems = VALUES('Table'[ID])
var _TableFiltered =  FILTER(ALL('Table'), NOT('Table'[ID] in _selectitems))

Return
CONCATENATEX(_TableFiltered, 'Table'[ID], UNICHAR(10))

// give a list like a CSV
List of ID = 
var _selectitems = VALUES('Table'[ID])
var _TableFiltered =  FILTER(ALL('Table'), NOT('Table'[ID] in _selectitems))

Return
TOCSV(_TableFiltered,,"-",FALSE())   

You can try some other different option but the main take away is that you cannot make the visual have the values directly from your table otherwise it will filter out the context, if you have a single visualization the context that is taken is on for the measure and nothing more.

You can also try and create an SVG that will give the table look and feel.

Please let me know if this helps.

[Andrey_Hamburg]

Hello @MFelix ,

thanks for the explantion. As far as I understand, I cannot reproduce exactly your solution because I use single table. But in the very beginning of your own post you said I could solve my issue without creating additional tables. So, I'm a bit irritated right now.

So, generally speaking, I should

- connect the slicer to a separated table

- create "subtables" out of my main table by filtering by filter selection (or NOT in filter selection)

Is it right?

Because if it is, it is exactly what I did in my own solution - see my initial post. 

[Andrey_Hamburg]

Hello @MFelix ,

thanks for you hint. At the present I do not have Calendar table,  I used "date_Column" as time hierarchy. That's why I created DISTINCT table for dates - to feed the slicer.

But what irritates me more is you screenshot. No matter how you interpret the slicer (1st January till 4th January or 1 January till 1 April) - on both  cases you should have more than 1 and 3 as selected items. Or, in another words, you should have less items in "not in selections". 

Can you please explain briefly how your idea works? Just for me to understand....

Kind regards from Germany

[MFelix]

Hi @Andrey_Hamburg,

I apologize I should have tell you this the dates are based on European format (dd-mm-yyyy) so this is January 1st to January 4rd

If you look at the the table to the left the values for january is only 1 and 3

Everything else is for dates after January.

I have made a small change to the dates made all of them in january:

On this print  you can see that the values for  january 1st to january 4th  are all of them except the 7 so the result is that the table only has value 7

If I change the date to the 2nd of January:

Now the 4, 5 and 7 are out of range are in the not in selection visual.

One additional advice is that when you are using date always create a date table for the slicing and dicing in order to be able to change the filter context if necessary and do time intelligence calculations.

[Andrey_Hamburg]

Hello @MFelix ,

I know that Calendar table is a must, but in this case I just hat to create a "very small report, a mockup", very fast... So, I skipped creating time dimension.

Thanks for you explanation with date. I will analyse you measure - I'm not familiar with SELECTCOLUMNS function.

Regards,

Andrey

[MFelix]

Hi @Andrey_Hamburg ,

If you need any other explanation please let me know.

[Andrey_Hamburg]

Hello,

I'm still trying to solve my task. Here again my start situation: I have a Warehouse table (let's name it "basictable"), consisting of 2 columns - Primarykey and "Date_Column".I use range slicer and I have to show filtered AND inverted filtered data in one report.

I created following prototype:
- calculated table with DISTINCT values from "Date_Column" - this is a basis for a range slicer. This table has two measures - MIN() and MAX(). In this way I intentd to get selected limits of the slicer. This table is NOT connected to any other

- 2 calculated tables - "filtered" and "inverted filtered" content:

[tblFiltered] = FILTER(ALL(basictable), 'basictable'[Date_Column] >= MIN && 'basictable'[Date_Column] <= MAX)

and

[tblnvertedFiltered] = FILTER(ALL(basictable), 'basictable'[Date_Column] < MIN || 'basictable'[Date_Column] > MAX)

So, my idea was  use time slicer to define limits of the range and display content of the filtered tables. Unfortunatelly, it does not work. The first table - "filtered" - shows all entries (no filtering at all, no matter how I set the slicer) and second table - "inverted filter" - remains empty. 

I also edited interactions: the slicer has no impact on both table visuals.

I obviuosly do something wrong. Can aonyone give me a hint?

Regards,

Andrey