[Python] How can I convert this inner join with multiple conditions to DAX code for PowerBI....

I basically have a query that uses my CDI table and self joins parts/rows of this table to other rows or parts of this table. I used CTEs to achieve this action in SQL shown below:

WITH ALC2_2 AS (
SELECT *
FROM CDI
WHERE QuestionID = 'ALC2_2' AND (StratificationID = 'B_F_ALL' OR StratificationID = 'B_M_ALL')
),

-- this table contains the questions related to binge drinking intensity
ALC4_0 AS (
SELECT *
FROM CDI
WHERE QuestionID = 'ALC4_0' AND (StratificationID = 'B_F_ALL' OR StratificationID = 'B_M_ALL')
),

-- this table contains the questions related to binge drinking frequency
ALC3_0 AS (
SELECT *
FROM CDI
WHERE QuestionID = 'ALC3_0' AND (StratificationID = 'B_F_ALL' OR StratificationID = 'B_M_ALL')
),

-- we self join all the tables on the condition that their
-- LocationID, StratificationID, YearStart, YearEnd are the same
BingeDrinkingIntFreq AS (
SELECT
ALC2_2.LogID,
ALC2_2.QuestionID AS AlcPrevID,
ALC4_0.QuestionID AS AlcIntID,
ALC3_0.QuestionID AS AlcFreqID,
ALC2_2.DataValue AS AlcPrevDataValue,
ALC4_0.DataValue AS AlcIntDataValue,
ALC3_0.DataValue AS AlcFreqDataValue,
ALC2_2.DataValueUnit AS AlcPrevDataValueUnit,
ALC4_0.DataValueUnit AS AlcIntDataValueUnit,
ALC3_0.DataValueUnit AS AlcFreqDataValueUnit,
ALC2_2.DataValueTypeID AS AlcPrevDataValueTypeID,
ALC4_0.DataValueTypeID AS AlcIntDataValueTypeID,
ALC3_0.DataValueTypeID AS AlcFreqDataValueTypeID,
ALC2_2.StratificationID,
ALC2_2.LocationID,
ALC2_2.YearStart,
ALC2_2.YearEnd,
ALC2_2.Population,
ALC2_2.TotalEvents AS BingeDrinkingPopulation,
(ALC2_2.TotalEvents * ALC4_0.DataValue) AS BingeDrinkingPopInt,
(ALC2_2.TotalEvents * ALC3_0.DataValue) AS BingeDrinkingPopFreq
FROM ALC2_2
INNER JOIN ALC4_0
ON (
(ALC2_2.DataValueTypeID = 'AGEADJPREV' AND ALC4_0.DataValueTypeID = 'AGEADJMEAN')
OR (ALC2_2.DataValueTypeID = 'CRDPREV' AND ALC4_0.DataValueTypeID = 'MEAN')
)
AND (ALC2_2.StratificationID = ALC4_0.StratificationID)
AND (ALC2_2.LocationID = ALC4_0.LocationID)
AND (ALC2_2.YearStart = ALC4_0.YearStart)
AND (ALC2_2.YearEnd = ALC4_0.YearEnd)
INNER JOIN ALC3_0
ON (
(ALC2_2.DataValueTypeID = 'AGEADJPREV' AND ALC3_0.DataValueTypeID = 'AGEADJMEAN')
OR (ALC2_2.DataValueTypeID = 'CRDPREV' AND ALC3_0.DataValueTypeID = 'MEAN')
)
AND (ALC2_2.StratificationID = ALC3_0.StratificationID)
AND (ALC2_2.LocationID = ALC3_0.LocationID)
AND (ALC2_2.YearStart = ALC3_0.YearStart)
AND (ALC2_2.YearEnd = ALC3_0.YearEnd)
)

SELECT
MAX(LogID) AS LogID,
AVG(AlcPrevDataValue) AS AvgAlcPrevDataValue,
AVG(AlcIntDataValue) AS AvgAlcIntDataValue,
AVG(AlcFreqDataValue) AS AvgAlcFreqDataValue,
LocationID,
StratificationID,
YearEnd,
YearStart,
AVG(BingeDrinkingPopInt) AS AvgBingeDrinkingPopInt,
AVG(BingeDrinkingPopFreq) AS AvgBingeDrinkingPopFreq,
AVG(BingeDrinkingPopulation) AS AvgBingeDrinkingPopulation
FROM BingeDrinkingIntFreq
GROUP BY LocationID, StratificationID, YearEnd, YearStart, AlcPrevID, AlcIntID, AlcFreqID


what I want to do is to be able to inner join this using DAX but so far all I've managed to do yet is replicate the CTEs

BingeDrinkingIntFreq =
VAR ALC2_2 = FILTER(
'chronic_disease_analyses_db main CDI',
'chronic_disease_analyses_db main CDI'[QuestionID] = "ALC2_2" && ('chronic_disease_analyses_db main CDI'[StratificationID] = "B_F_ALL" || 'chronic_disease_analyses_db main CDI'[StratificationID] = "B_M_ALL")
)

VAR ALC4_0 = FILTER(
'chronic_disease_analyses_db main CDI',
'chronic_disease_analyses_db main CDI'[QuestionID] = "ALC4_0" && ('chronic_disease_analyses_db main CDI'[StratificationID] = "B_F_ALL" || 'chronic_disease_analyses_db main CDI'[StratificationID] = "B_M_ALL")
)

VAR ALC3_0 = FILTER(
'chronic_disease_analyses_db main CDI',
'chronic_disease_analyses_db main CDI'[QuestionID] = "ALC3_0" && ('chronic_disease_analyses_db main CDI'[StratificationID] = "B_F_ALL" || 'chronic_disease_analyses_db main CDI'[StratificationID] = "B_M_ALL")
)

VAR Joined = <some code here I don't know what yet>

RETURN Joined


The joins I've seen in DAX is always limited to simple conditions but can I have a join that does it on multiple ones like the SQL query above?

Continue reading...