Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Userelationship not using inactive date relation

I have the following relationships in my data model:

 

 

There is an active relationship between DimCalendar[Datum] and FactAppointment[Datum afspraak]. There is an inactive relationship between DimCalendar[Datum] and FactAppointment[Boeking afspraak]. 

 

When using the following DAX I get the wrong results: 

NP = 

var numberOfNewPatients = 
CALCULATE(DISTINCTCOUNT(FactAppointment[PatientID]),
FILTER(FactAppointment, FactAppointment[EarliestDateBoeking] = TRUE() && FactAppointment[EarliestDateAfspraak] = TRUE()))

var finalMeasure = 
CALCULATE(numberOfNewPatients, USERELATIONSHIP(FactAppointment[Datum boeking], DimCalendar[Datum]))

RETURN finalMeasure

 
When I manually deactivate the relationship between DimCalendar[Datum] and FactAppointment[Datum afspraak] and activate the relationship between DimCalendar[Datum] and FactAppointment[Boeking afspraak] I get the correct result. 

 

Does anyone know what I might be doing wrong?

  • Anonymous You can't use a VAR with CALCULATE that way. You would have to have numberOfNewPatients as a separate measure and then your finalMeasure as a separate measure. CALCULATE is not going to recalculate a VAR because VAR's are static (I know that doesn't make sense but it is no less a fact).

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    Anonymous You can't use a VAR with CALCULATE that way. You would have to have numberOfNewPatients as a separate measure and then your finalMeasure as a separate measure. CALCULATE is not going to recalculate a VAR because VAR's are static (I know that doesn't make sense but it is no less a fact).