How to count number of rows for entries which are not related?

[manojgour]

I am looking to count rows from table  name - 'Appointment' which was 'Cancelled' on a given Slot. Once appointment is cancelled that entry is deleted from table - 'Slot'

Relationship between two tables:

Appointment[PK-appointmentId, FK-slotId] -> Slot[PK-slotId]

Active relationship shown in below screenshot-

Below screenshot is the detail from table when an appointment is 'Cancelled' and how msemr_primaryslot(FK) in Appointment table is null
 

Report has a 'Date Slicer' using Slot[Slot date] and when we choose date from slicer, measure wouldn't be able to navigate to count rows with Cancelled Status from Appointment[table]

I tried using function USERELATIONSHIP with inactive relationship on 'Appointment'[Appointment date] -> 'Slot'[Slot Date] but it would return empty.

PS - I have to use Slot [Slot date] for date slicer for other visuals and measurements to work.

Any help or suggestions?

[manojgour · Accepted solution]

Thanks for your reply @v-yiruan-msft 

I was able to resolve this problem with changes in Data modeling and using USERELATIONSHIP dax function.
This time I changed relationship to inactive between Slot and Appointment tables whereas now both the tables relationship with Calendar table was made active to achieve other measures to work in report as usual.
Cancelled count calculation measure started to drive from Slot[date]->Calendar[date]->Appointment[date] route and my other matrix calculation for other appointment status made use of dax function -

USERELATIONSHIP('Appointment'[FK_slotid], Slot[PK_slotid]) )

[v-yiruan-msft]

Hi @manojgour ,

Could you please validate that is there any record exist in table Slot when appointment is 'Cancelled', same slotid with the one in table Appointment and 'Appointment'[Appointment date] equal 'Slot'[Slot Date]? Besides that, please check whether the data type of 'Appointment'[Appointment date] and 'Slot'[Slot Date] are same or not? Could you please provide some sample data(exclude sensitive data) in order to make troubleshooting? It is better if you can provide your sample pbix file(please clear sensitive data). Thank you.

On the other hand, please review the content in the following links. Hope they can help you resolve the problem.

Cannot create multiples column relationship between two tables

Use Multiple Connections Between Tables

Best Regards