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.
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.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
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!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.