Can you dax with out ioin

[Rohini]

Hello PBI Folks,

Could you throw some help at me with DAX and Model, with the below set up I got to work DAX2 working but seeking help on DAX1.

Do I need to change the model to make DAX1 work or will it work with no appropriate join between zone and employee table ?

Zone Table		(JOIN - ZONE to ZONE)	Department Table		(JOIN - DEP to DEP)		TicketTable	(JOIN -EMPID to EMPID)		EmpTable
Zone	Head		Zone	Dep		Dep	EmpID		Zone	EmpID
East	A		East	D1		D1	E1		East	E1
West	B		East	D2		D1	E2		East	E2
South	C		West	D3		D2	E3		East	E3
North	D		South	D4		D2	E4		East	E4
MidWest	E		South	D5		D4	E5		South	E5
MidEast	D		North	D6		D5	E6		South	E6
			North	D7		D7	E7		North	E7
			North	D8		D8	E8		North	E8
			North	D9		D9	E9		North	E9
			North	D10		D10	E10		East	E11
	DAX?	DAX ?
Zone	AllEmployeeCount(from Emp Table)	EmployeeCountwithTickets(from Ticket Table)
East	6	4
West	0	0
South	2	2
North	4	3
MidWest	0	0
MidEast	0	0

[v-xicai]

Hi @Rohini ,

For the relationship, recommend you create the star schema instead of circle schema, and change the Cross filter direction of relationships among the these tables above from Single to Both , which will take these tables treated as a single table.

Then you may create measure like DAX below.

DAX1= CALCULATE(COUNT(TicketTable[EmpID]),FILTER(ALLSELECTED(TicketTable), TicketTable[Dep]IN FILTERS (DepartmentTable[Dep])), FILTER(ALLSELECTED(DepartmentTable), DepartmentTable[Dep]IN FILTERS (TicketTable[Dep]) && DepartmentTable[Zone]IN FILTERS (ZoneTable[Zone])))



DAX2= CALCULATE(COUNT(EmpTable[EmpID]),FILTER(ALLSELECTED(EmpTable), EmpTable[EmpID]IN FILTERS (TicketTable[EmpID]) && EmpTable[Zone]IN FILTERS (ZoneTable[Zone])))

Best Regards,

Amy 

Community Support Team _ Amy

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

[amitchandak]

@Rohini , refer https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

or in power query

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[AllisonKennedy]

DAX always looks at the entire context of your report, including Joins. I'm not really clear what the department table is doing or why it's needed? 

You will need to join the Zone table to the Employee table in some way.

Without knowing more about your data and requirements I can't say for certain what the best suggestion is, but you need to consider which tables are 'fact' and which are 'dimension' and also how they are used in the report. You may need to create an inactive relationship and use the DAX USERELATIONSHIP, but I don't think that's needed here. 

[Rohini]

@AllisonKennedy 

Let me explain more.

Department table has more attributes that are required so it cannot go away.

Employee table is already joined to TicketTable and think both of them as transactional but they need to be joined for DAX2=EmployeeCountwithTickets(from Ticket Table)

Employee table could be joined to Zone table on Zone field , so this second active join is not allowed correct ?

So put me on the right track here with additional help.

1. Create an inactive join and write userrelationshop dax function for DAX1=AllEmployeeCount(from Emp Table)

Can you help roughly with that ?

2. Switch Emp to Ticket table join to inactive and Join Emp to Zone table active on Zone field and then write userrelationship dax function for DAX2=EmployeeCountwithTickets(from Ticket Table)

Zone is master table

Emp and Ticket tables are transactional

Which is the better performance tuned solution

Or other even better alternatives ?

I cannot use power query due to other constraints like live query mode.

[Rohini]

DAX1=AllEmployeeCount(from Emp Table)

DAX2=EmployeeCountwithTickets(from Ticket Table)