Hi,
I've got three tables in PowerBI TableA, TableB and TableC. Relationship is one-to-many in the order TableA --> TableB --> TableC.
I want to have a measure in tableA that will use rows of TableC
I'm trying to get DAX equivalent of the below SQL. Any help will be highly appreciated as I haven't had much success.
DECLARE @CurrentPeriodStartDate DATE = DATEADD(DAY,(DATEPART(DAY,DATEADD(MONTH,-1,GETDATE()))*-1)+1,DATEADD(MONTH,-1,GETDATE())) -- 2021-05-01
DECLARE @PeriodStartDate DATE = DATEADD(DAY,(DATEPART(DAY,DATEADD(MONTH,-4,GETDATE()))*-1)+1,DATEADD(MONTH,-4,GETDATE())) -- 2021-02-01
DECLARE @PeriodEndDate DATE = DATEAdd(month,2,@PeriodStartDate) -- 2021-04-01
DECLARE @QuarterTotal INT =
(
select COUNT(*) from TableA a
inner join TableB b
on a.Id = b.TableAId
inner join TableC c
on b.Id = c.TableBId
where c.PeriodStartDate between @PeriodStartDate and @PeriodEndDate
)
DECLARE @CurrentPeriodTotal INT =
(
select COUNT(*) from TableA a
inner join TableB b
on a.Id = b.TableAId
inner join TableC c
on b.Id = c.TableBId
where c.PeriodStartDate = @CurrentPeriodStartDate
)
Measure in TableA = SELECT CASE WHEN @QuarterTotal = 3 AND @CurrentPeriodTotal = 1 THEN 0
ELSE 1 END
Solved! Go to Solution.
with the relationship, just simply use RELATEDTABLE to get rows of TableC
with the relationship, just simply use RELATEDTABLE to get rows of TableC
Thanks. I got the solution using RELATEDTABLE.
@ryan_smith_ww , TableA, TableB, and TableC should in power bi on keys.
You need to have date tables. 2 are needed here. One joined with periodstartdate of Table3
refer this video, how you can get two-period filters
How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg
Thanks @amitchandak . TableA, TableB and TableC are in PowerBI with relationships defined between them. Unfortunately I'm still not clear on how do I convert the SQL shared above to equivalent DAX for the measure.
@ryan_smith_ww , Please share relationship digram, based on that I suggest
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!