Dynamic Dax Formula Required In Daily Visit Staff Report

[Kumar Shanmugam]

Desired output:

	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30
STAFF	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Total
A	2	L	TC		1	1																									4
B	1	CC	L		TC	1																									2
C	H	H	1		1	TC																									2
Total	3		1		2	2																									8

I am working on showing daily visit report of my staff daywise.

1. I am able to show daily visit count by dax formula: countrows(visit details)
2. Problem area is how to get non visit reason code, if staff did not do visit on that particular date. Require dynamic dax formula to help me on the same. I don't know how to connect non visit date (fact table) with visit details (fact table) report as both are not dimension table.

SHEET NAME:
VISIT DETAILS (FACT TABLE) => contains daily visit details of staff
STAFF DETAILS (DIMENSION TABLE) => contains staff details
NON VISIT DATE (FACT TABLE) => contains details of date where staff unable to do visit and reason for the same.
LOAN DETAILS (DIMENSION TABLE) => contains details of laon accounts allocated to staff for visit purpose
DATE (DIMENSION TABLE) => contains fY date details.

RELATIONSHIP CRITERIA:
1. VISIT DETAILS table related to STAFF DETAILS table by staff name column. (many to one relationship)
2. VISIT DETAILS table related to DATE table by visit date column. (many to one relationship)
3. VISIT DETAILS table related to LOAN DETAILS table by loan number column (many to one relationship)

 

[vletm]

Kumar Shanmugam
You should reread Forums Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

Please Don't

• PLEASE DON'T SHOUT! We have big ears and will hear you just the same.

You should modify above writings as soon as possible like
Sentences start with capital letters ...