Power BI – reporting on Items NOT in a filter

We are working on a report to look at Power BI activities and objects at the moment.

Lets have a look at the model

This is a test model with a date dimension (When the Activity occured)

• A Report dimension (Details of the report used in the activity)
• A Dataset dimension (Details about the dataset used in the activity)
• And a Fact table of Activity metrics

We have a star schema with single direction joins to the fact. Power BI as we know loves a star.

Lets have a look at my sample data

Date

Just a sample of the date table

DataSet

Report

Fact

DataSet 3 and report 4 have never been part of an activity (Just for this exercise 01/01/2000 is our fake date because there is no activity)

The above logic is easy to do (Which items have never been used) but what happens when you want to look at the business question

Give me all the reports NOT used in September

As we can see from the metrics table report 2 and 4 were not used in September

So how do we create DAX that will allow us to look at these kind of Business Questions?

Base DAX

We want some base DAX measures that we can then build on top of

# reports = DISTINCTCOUNT(ActivityMetrics[ReportKey])

# Datasets = DISTINCTCOUNT(ActivityMetrics[DatasetKey])

And for the reports we never use we can either use the measure above with a filter

Or explicitly add a filter into a new measure

# Reports Never Used = CALCULATE([# reports],DimReport[neverusedFlag]=true)

# Datasets Never Used = CALCULATE([# Datasets],DimDataset[neverusedFlag]=true) 

Notice that I set Edit Interactions to off between the date slicer and the two cards because the cards are simply telling me how many reports and dashboards have never been used

So this part of the process is all fine.

Our Next Business Question is

Show me the reports and datasets used in September by date

This is another straight forward request

And both interact with the Date Filter.

And now to the question

Show me the reports and datasets NOT used in September

So we use our date slicer and we want to see items outside of that date slicer. We need to create a more complex measure to do what we need

Is Not in the Date Filter Flag = 

// assumes a slicer on DimDate[ActivityDateKey] and there is an implicit filter on the visual 

VAR VActivityDates = SELECTCOLUMNS(DimDate,"Dates", DimDate[DateKey]) 

VAR VDates = CALENDAR(MINX(DimDate,DimDate[DateKey]),MAXX(DimDate,DimDate[DateKey]))

RETURN IF(COUNTROWS(INTERSECT(VActivityDates,VDates)),0,1) 

// if no matching rows, return 1

Lets break the above down a little to understand what is happening

SELECTCOLUMNS

VAR VActivityDates = SELECTCOLUMNS(DimDate,”Dates”, DimDate[DateKey])

First of all create a variable. SELECTCOLUMNS

“Adds calculated columns to the given table or table expression.”

We start with the table they come from. then we give the name to the column in SELECTCOLUMNS “ColumnName”, Next comes the expression which in this case is the column reference.

CALENDAR

VAR VDates = CALENDAR(MINX(DimDate,DimDate[DateKey]),MAXX(DimDate,DimDate[DateKey]))

Next comes the VDates Variable. Calendar Returns a table with a single column named “Date” and it contains a contiguous set of dates. We need to provide the range of dates. In this case MINX finds the earliest date and MAXX finds the Maximum date in our date dimension

Now this should work with our slicer. In this case Min 01/09/2021 Max 30/09/2021

INTERSECT

INTERSECT(VActivityDates,VDates)

And now we get to the measure that will be RETURNED

“INTERSECT A table that contains all the rows in table_expression1 that are also in table_expression2”

COUNTROWS

COUNTROWS simply counts the number of rows within a table or an expression

IF

RETURN IF(COUNTROWS(INTERSECT(VActivityDates,VDates)),0,1)

So here we can see the intersect and if working together. If there are rows counted (True – There are rows then 0 else 1.

Still Struggling to understand? Me too. Lets draw it

Here is where I am a little confused. I’m not sure which part of the DAX works with the slicer. SELECTCOLUMNS or CALENDAR MINX MAXX? I’m making the assumption that CALENDAR takes the min and max of the slicer but its incredibly difficult to test so if anyone has any more insight on this that would be fantastic.

And we can now look at everything together in a report

We have used Is Not =1 in the ‘Is not in the Date Filter’ as out value and its always ever 0 or 1

But now we are asked another business question

How MANY reports did we not view in the current date slicer?

with the measure as it stands. It needs the Report Name or the Data set name to provide details

We can’t use what we have in a card because we cant add the implicit filter (Report Name etc).

So How can we do this one?

We can solve it using variables

Total Reports not in date Filter = 
//Clear all filters which are applied to the specified table.ALLCROSSFILTERED
var VAllReports =CALCULATE([# reports],ALLCROSSFILTERED(DimDate))
//Total within the date filtervar 
VInFilter = [# reports]
//All reports - Reports inside the slicer
Return VAllReports-VInFilter

Using ALLCROSSFILTERED brings back the total reports 4 as it removes all the filters

then we get back the total of reports in the date slicer and finally take in filter away from all the reports.

Out # reports is a measure made from distinct report IDs already. We now have everything we need to create these reports thanks to DAX.