Trying to get rid of a filter on a measure, and not understanding the behaviour I get

[Elorian]

Hello,

I have an issue to get rid of the effect of a time-based slicer on a measure displayed on the same page than graphical visuals à I want the slicer to apply on my graphs, I want the measure to be independent of it.

More details :

• I have a table [Tickets Created] with a list of [Ticket Reference] created on a certain date à in that table, I have also a column [Year-Month] in that table (format text), build based on the creation dates of the tickets
• I have a table [Time Table], with all the Years and Months covering the dates of creation of the tickets, containing as well a column [Year-Month] (format text) making the link with my 1st table and another column [Year-Month2] which is the same but format date (YYYY-MM) --> it's simply a date table with years and months and concatenation of both, basically.
• I have a slicer (Timeline 2.4.0) based on [Time Table].[Year-Month2] allowing me to select the period I display, on bar charts, the distribution of creation of the tickets
• I have a measure that I display via a card in which I want to give the average of tickets created during the last month (my last month in the data is “2021-07”, and evolves months after month, of course)

Everything was ok until I introduced the slicer --> the graphs are responding well, but of course, the result of the measure is influenced by the period selected in the slicer ==> I want to get rid of that effect on my measure.

I made multiple tries without success (but I’m a newbie), and there is something I don’t get by making some tests on a separate page on which I put the slicer, selected in it a previous month (not the last one, of course), and created some test measures as follows :

• Test 1 = CALCULATE(COUNT(‘Tickets Created’[Ticket Reference]),ALL('Time Table'[Year-Month2]) --> it gives me the total number of created tickets, without taking into account the value of the slicer ==> OK!
• Test 2 = CALCULATE(COUNT(‘Tickets Created’[Ticket Reference]),ALL('Time Table'[Year-Month2]), FILTER(ALL(‘Tickets Created’[Year-Month]), ‘Tickets Created’ [Year-Month] = "2021-07")) --> it gives me the total number of created tickets during my last period that I hardcoded here, without taking into account the value of the slicer ==> OK! But I don’t want to hardcode, obviously… Thus :
• I created : Test 3 = CALCULATE(MAX(‘Tickets Created’[Year-Month]),ALL('Time Table'[Year-Month2])) ==> OK, when displayed in a card, it shows me well “2021-07” and Power BI indicates me that it’s a text format
• So, I replace my hard code in Test 2 as follow :

Test 2 = CALCULATE(COUNT(‘Tickets Created’[Ticket Reference]),ALL('Time Table'[Year-Month2]), FILTER(ALL(‘Tickets Created’[Year-Month]), ‘Tickets Created’ [Year-Month] = [Test 3])) ==> KO!! It gives me the same result as Test 1 and I don’t understand! o_O

I tried other things, but nothing seems to work, and I’m in a dead-end.

Can you please help?

Many thanks in advance!

Elorian.

[amitchandak]

@Elorian , based on what I got so far

is this Time Table is date table joing on ticket created date in ticket table?

If so

You can have measures like

MTD Tickets = CALCULATE(COUNT(‘Tickets Created’[Ticket Reference]),DATESMTD('Date'[Date]))
last MTD Tickets = CALCULATE(COUNT(‘Tickets Created’[Ticket Reference]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Or use a date table

refer in case you have 2 dates to deal with

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Elorian · Accepted solution]

Hello,

I just made another test : instead of using, in my measure Test 2, the measure Test 3 in the FILTER, I created a variable containing exactly the same thing than measure Test 3, and used it in the FILTER instead of measure Test 3 --> I get the correct result!

I still don't understand in what it is different and why the result is not the same, but I still have a solution to my issue.

Kr,

Elorian. 

[Elorian]

Hello,

Yes, the "time table" I mention is indeed a date table related to my fact table. It has, amongst others, columns Year, Month, Year-Month (text format) and Year-Month2 (date format).

Thanks for the measure you give me, but this doesn't fit my need... And it doesn't explain the result I get with my test measures I describe below.

Kr,

Elorian.