DAX in 1st table for data in the third table. (SQL query given. Looking for Equivalent DAX)

[ryan_smith_ww]

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

[wdx223_Daniel · Accepted solution]

with the relationship, just simply use RELATEDTABLE to get rows of TableC

[ryan_smith_ww]

Thanks. I got the solution using RELATEDTABLE.

[amitchandak]

@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

[ryan_smith_ww]

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.

[amitchandak]

@ryan_smith_ww , Please share relationship digram, based on that I suggest

[ryan_smith_ww]

@amitchandak Here's the basic diagram for the Three tables. Thanks for your help.