Crossfilter not working with filter in calculate

[Anonymous]

Hi All,

My DateDim is joined with the Account table using Open Date. I'm trying to find how many accounts are open at each month, hence using the Account[Opend Date]< End of Month and Close Date is empty or > the End of Month, which is what the Filter function for.

I want to ignore the pre-existing relationship between the DateDim Date and Account[Open Date] by using the Crossfilter(none).  But this is not working at all, the relationship is still in active and filtering all accounts open in the month. 

Where did I do wrong and how should I chagne it? 

#Open = 
var __EoM = ENDOFMONTH(DateDim[Date])
return
CALCULATE(
    COUNT(Account[ID])
    ,FILTER(Account
        ,Account[Open Date]<=__EoM
        &&or(Account[Date Closed]>__EoM,ISBLANK(Account[Date Closed]))
		)
    ,CROSSFILTER(Account[Open Date],DateDim[Date],None)
	)

[okusai3000]

I was just in the same problem. For someone with this kind of issue, the solution is to use a calculate inside of another calculate. Here's a sample:

*Adh SAHS 4hs MS3 =

CALCULATE(

  CALCULATE('fact prescripciones'[Pacientes Activos],

    filter(all('fact Monitorizacion'[*Fecha Recogida]),LASTDATE('fact Monitorizacion'[*Fecha Recogida])<=max('dim     Fecha'[Fecha])),

    filter(all('fact Monitorizacion'[*Fecha Recogida]),LASTDATE('fact Monitorizacion'[*Fecha Recogida])>=min('dim Fecha'[Fecha])),

    'fact Monitorizacion'[*Adh SAHS 4hs]="cumple"),

  CROSSFILTER('fact Prescripciones'[Id Prescripcion],'fact Visitas'[Id Prescripcion],NONE))

cheers

[Mariusz]

Hi @Anonymous 

Please see the below code.

measure = 
VAR m = MAX(DateDim[yearMonthNo]) -- concatinated Year month 
RETURN 
CALCULATE(
    COUNTROWS(Account),
    FILTER(
        ALL(DateDim),
        'DateDim[yearMonthNo] = m
    ) 
)

Hope this helps.
Mariusz

[Anonymous]

Thanks Mariusz for your help, this did not work in this case. 

I still need to keep the Filter ( ) function to the Account table by the Open Date and Closed Date from the account table. 
 

I just wish to find a way to inactive the pre-existing relationship: DateDim[Date] = Account[Open Date]

Thanks again. 

[Mariusz]

Hi @Anonymous 

Please see the below code.

measure = 
VAR m = MAX(DateDim[yearMonthNo]) -- concatinated Year month 
RETURN 
CALCULATE(
    COUNTROWS(Account),
    FILTER(
        ALL(DateDim),
        'DateDim[yearMonthNo] = m
    ) 
)

Hope this helps.
Mariusz