Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using two FILTER command in one CALCULATE

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'[Fl...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
v-easonf-msft
Community Support
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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.