Forum Discussion
How to create a DAX measure for this SQL query?
I have an calculation that is dead simple in SQL, but I am having no success in getting a DAX measure to do the same.
declare @FromDate date = '2020-08-01';
declare @ToDate date = '2020-08-31';
with lia as
( -- Get list of accounts that meet the criteria
select distinct AccountNo
from factLoan_Day fld
where dimDate between @FromDate and @ToDate
and Flag1 = 'Y'
)
-- For that list of accounts, divide the cash collected by the amount due
select SUM(case when dtt.IsCashCollected = 'Y' then ft.transactionamount end)
,SUM(case when dtt.TransactionType= 'DUE' then ft.transactionamount end)
,SUM(case when dtt.IsCashCollected = 'Y' then ft.transactionamount end) / SUM(case when dtt.TransactionType = 'DUE' then ft.transactionamount end)
from lia
join factTransaction ft on ft.AccountNo = lia.AccountNo
join dimTransactionType dtt on dtt.dimTransactionType = ft.dimTransactionType
where ft.dimDate between @FromDate and @ToDate;
Any ideas?
2 Replies
Hi Anonymous ,
How do you have you model setup in Power BI do you also have all the tables you refer on the SQL query? And do you have any relationship between the tables?
Based on that the DAX syntax may be different and in some cases may not even be necessary to make complex calculations because measures are based on context.
If you can share some sample data and expected result woud be great.
Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).
Anonymous
You can use SWITCH Function with Calculate and Filter!
If you have the sample data then its much easier to explain!
