Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rfernandes
Helper I
Helper I

Remove Date filters within a date range

Hello everyone, 

I trying to ignore year and month filters, but inside a range of time. 

I working with a dabase that have a few years, the chart below shows the distinct count of every order ID. 

All years.png

For this measure i only want to work with year 2015 and 2016. 

Selected Years.png

My goal is to show  the table below, ignoring year and month filters. For example: Asia ll show 1006 in every month column(total of 2015 and 2016) and not 3687 (all years)

Result Table.png

 

With that, i want to ignore regions that have few orders ID based on year 2015 and 2016.

I tried a few things, but i can´t make it work.

 

Total sales = 
VAR id_qnty = CALCULATE(DISTINCTCOUNT(db[Order ID]); ALL(db[Date]); db[Date].[YEAR] in {2015;2016})
return
IF(id_qnty>1000; SUM(db[Total Revenue]);BLANK())

Here is the example file: example.pbix

 

Thank you!

3 REPLIES 3
Anonymous
Not applicable

What if you try the following:

 

1. Create a Calendar Table with the following code:

DimCalendar = 
ADDCOLUMNS(
    CALENDAR( 
        DATE( YEAR(MIN( db[Date])),1,1),
        DATE( YEAR( MAX ( db[Date])),12,31)),
        "Year", YEAR([Date]),
        "Month", MONTH( [Date] ),
        "MonthName", FORMAT( [Date], "mmmm")
)

Relate that table to your db table (1:M) on the date column from each

 

Use Year and Month for that new table as columns and rows on a new visual, like a matrix. Put Region on rows from the db table. Dont want to use the date columns from the db table, we want to filter the Calendar table and let that flow down to the db table.  

 

Add a Year slicer from the DimCalender table. Select a range from 2015 to 2016.  

 

Now we can just add this measure:

Measure = 
SUMX( Values( db[Region]),
IF(
    CALCULATE( [linhas], ALLSELECTED(DimCalendar))> 1000, 
    CALCULATE( [linhas], ALLSELECTED(DimCalendar))
)
)

and the final table

Final Table.png

Thank you for you answer @Anonymous !

But is there a way without using a calendar table? My model only base with one date column.

 

With the calendar table i used:

Measure 2 = 
VAR xxx = CALCULATE( [linhas]; ALLSELECTED(DimCalendar))

return IF(xxx>1000;Sum(db[Total Revenue]))

I didn´t understand the part 

SUMX( Values( db[Region])

 Thank you

Anonymous
Not applicable

Probably could work without date calendar, but it's generally not a good idea to use columns from your Fact table. You really want to use smaller Dimension table ( like the calendar table) to filter the fact table. 

 

The reason for the SUMX (Values..) in the front of that measure is for the grand totals. Without that I believe it would be the grand total for all the regions. But with SUMX ( Values( Region)) in the grand total it will just be the Regions that are visible in the current filter context, so just those three regions will be used to sum. 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.