cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
manojgour
Microsoft
Microsoft

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

 

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-

 

manojgour_0-1623440726060.png

 

 

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

manojgour_1-1623440726062.png

 

 

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?

1 ACCEPTED SOLUTION
manojgour
Microsoft
Microsoft

Thanks for your reply @yingyinr 

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]) )

 
 

manojgour_0-1623694095872.png

 

 

View solution in original post

2 REPLIES 2
manojgour
Microsoft
Microsoft

Thanks for your reply @yingyinr 

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]) )

 
 

manojgour_0-1623694095872.png

 

 

View solution in original post

yingyinr
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.