Using two FILTER command in one CALCULATE

[Anonymous]

Hello Community,

Here is the DAX formula I have which is not working :

Count Year-1 = 
CALCULATE(DISTINCTCOUNT('FACT_TABLE'[ID]),FILTER(
'FACT_TABLE',OR('FACT_TABLE'[Flag1] = 1,'FACT_TABLE'[Flag2] = 1)),
FILTER(ALL('DATE_TABLE'),'DATE_TABLE'[Date] = MAX('DATE_TABLE'[Date] ) - 1))

As you can see, I'm trying to count ID with condition on two different column. That's why I'm using FILTER at first sight and it is returning what is expected.

My problem here is I have to display those data on YEAR-1 and getting out of context at the same time.

That's why I'm using the FILTER(ALL()) with a MAX, the MAX function giving me the value of the date filtered in a dropdown box.
The result I get here is a blank... And that's why I'm asking help here. To me, this should work.

Moreover, this is working with an ALL(EXCEPT()) statement in another measure, but obviously returning me the count of all the data whereas here I just need the filtered data (I am using a page Filter on a COMPANY field).

Also, if I force the date in the formula as the one in the filter (for example, FILTER(ALL('DATE_TABLE'),'DATE_TABLE'[Date] = 2017 and the dropdown filter is set on 2017), this work perfectly, but again I need the previous year to be displayed.
It's like the first FILTER statement is creating me a table based on the outside filter, which I don't want to.

I've tried ALL, ALLSELECTED, ALLEXCEPT and all those kinda tips, but did not manage to make it work.

Thank you for your help,
Heykel

[Anonymous · Accepted solution]

I finally managed to solve my issue, with a solution I wouldn't use, but... I'm struggling.

What I did is creating a new colum, with the result of OR(Flag1 = 1, Flag2 = 1).

Then I replaced this :

FILTER(
'FACT_TABLE',OR('FACT_TABLE'[Flag1] = 1,'FACT_TABLE'[Flag2] = 1))

 By this : 

Flag3 = true

 Avoiding the use of FILTER statement did the trick.

[v-easonf-msft]

Hi， @Anonymous 

Have you create a relationship between DATE_TABLE and FACT_TABLE?

What type of your  DATE_TABLE[Date]? If its data type is "Date",try to  use  DATE_TABLE[Date].[Year] rather than 
DATE_TABLE[Date] in your measure.

And  if here is no relationship between tables,please try measure as below:

Count Year-1 = 
VAR _maxdate =
    MAX( DATE_TABLE[Date].[Year] ) - 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'FACT_TABLE'[ID] ),
        FILTER ( 'FACT_TABLE', OR ( 'FACT_TABLE'[Flag1] = 1, 'FACT_TABLE'[Flag2] = 1 ) ),
        FILTER (
            ALL ( FACT_TABLE[Date].[Year] ),
            'FACT_TABLE'[Date].[Year] = _maxdate
        )
    )

Please check my pbix file for more details.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Than you for the reply.

Well, my field "Date" is already a year in fact. I just simplified the example.

So I just have to minus 1 the max (to get the year-1).

This part is working, this is not the issue.

And yes the fact table is related to the date table of course.

In your PBIX you did not put any box filter, I don't think you get my point.

In my case, I'm doing DISTINCTCOUNT on ID to count people registered for a specified Year in a filter (2018 for example).

Then I put this measure in a Table, and put next to this measure another one which is doing the calculation of the previous year (2017 in my example).

I managed to do this in every pbix easily so far.

Here, using the filter is on the fact table to get the logical result of my two boolean seems to also filter data on the selected year in the BOX.

That's why when I try to display these data on the YEAR-1, I get nothing, because data where filtered on 2018 (so no data for 2017).

This is the first time I get this behavior and I don't know how to work around it.

[Anonymous]

Thank you for the reply.

I also tried this but it didn't help.

As I said, when I filter in the DAX formula on a different date from dropdown box filter, the result is blank.

And I have to use the FILTER statement to filter on two different columns (which is not in your example).

[SteveCampbell]

You need to use a variable so you can step back out of the current filter context

Count Year-1 = 
var _maxdate = MAX('DATE_TABLE'[Date] ) - 1

RETURN 

CALCULATE(
DISTINCTCOUNT('FACT_TABLE'[ID]),
('FACT_TABLE'[Flag1] = 1 || 'FACT_TABLE'[Flag2] = 1),
'DATE_TABLE'[Date] =  _maxdate)