Forum Discussion

tloF2D5U9E5's avatar
tloF2D5U9E5
Regular Visitor
8 years ago
Solved

Direct Query - Measure that applies filters like a slicer

Is there a way to create a measure that applies filters in direct query in the same way as a slicer? I have found that the SQL generated from a CALCULATE statement is different from the SQL generated by performing the same task using a slicer.

 

 

For illustration purposes, let's imagine there are 3 tables in direct query mode (Ledger, CompanyTable, and AccountTable). If I sum the "Currency" column in Ledger, and apply filters using a slicer to both other tables, it generates a query like this:

Scenario 1 - Slicers Only

Select sum(T1.Currency)
FROM
	(SELECT Currency ,FK_Companies , FK_Accounts from Ledger) as [T0]
Left Outer Join
	(SELECT CompanyName ,PK_Companies from CompanyTable) as [T1]
	On T0.FK_Companies = T1.PK_Companies
Left Outer Join
	(SELECT Accounts ,PK_Accounts from AccountTable) as [T2]
	On T0.FK_Accounts = T2.PK_Accounts
WHERE
	T1.CompanyName in (N'Company1',N'Company2')
	and T2.Accounts in (N'100',N'101',N'102')

If I leave the slicer on the company table and instead try try to apply the same filter for Accounts using a calculate statement:

=Calculate(sum(Ledger[Currency]),filter(AccountTable,AccountTable[Accounts]="100" || AccountTable[Accounts]="101" || AccountTable[Accounts]="102"))

It will generate a query like this:

Scenario 2 - Slicers + Filter

Select sum(T3.Currency)
From
	(
	Select FK_Accounts, sum(T1.Currency)
	FROM
		(SELECT Currency ,FK_Companies , FK_Accounts from Ledger) as [T0]
	Left Outer Join
		(SELECT CompanyName ,PK_Companies from CompanyTable) as [T1]
		On T0.FK_Companies = T1.PK_Companies
	WHERE
		T1.CompanyName in (N'Company1',N'Company2')
	Group By FK_Accounts
	) [T3]
Inner Join
	((SELECT Accounts ,PK_Accounts from AccountTable) as [T2]
	Where T2.Accounts in (N'100',N'101',N'102')
	) [T4]
	On T3.FK_Accounts = T4.PK_Accounts

Scenario 2 is obviously less efficient. How can I write a CALCULATE statement so that I get the first result in direct query.

 

Thanks!

  • Okay,  I figured it out. You have to apply the filter statement like this:

    filter(Ledger,
    related(AccountTable[Accounts])="100" || 
    related(AccountTable[Accounts])="101" || 
    related(AccountTable[Accounts])="102"
    )

    Seems clunky... if anybody knows a more efficient way, I'd be interested to hear it!

1 Reply

  • tloF2D5U9E5's avatar
    tloF2D5U9E5
    Regular Visitor

    Okay,  I figured it out. You have to apply the filter statement like this:

    filter(Ledger,
    related(AccountTable[Accounts])="100" || 
    related(AccountTable[Accounts])="101" || 
    related(AccountTable[Accounts])="102"
    )

    Seems clunky... if anybody knows a more efficient way, I'd be interested to hear it!