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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Crossfilter not working with filter in calculate

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)
	)
4 REPLIES 4
okusai3000
Helper IV
Helper IV

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
Community Champion
Community Champion

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
Not applicable

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
Community Champion
Community Champion

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.