Как я могу преобразовать это внутреннее соединение с несколькими условиями в код DAX для PowerBi. Функции соединения в D ⇐ Python

[Anonymous]

У меня в основном есть запрос, который использует мою таблицу CDI и самостоятельно соединяет детали/строки этой таблицы к другим строкам или частям этой таблицы. Я использовал CTE для достижения этого действия в SQL, показанном ниже: < /p>

Код: Выделить всё

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
< /code>
То, что я хочу сделать, - это иметь возможность внутренней присоединиться к этому с помощью DAX, но пока все, что мне удалось сделать, - это воспроизведение CTE < /p>
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 = 

Подробнее здесь: [url]https://stackoverflow.com/questions/79645150/how-can-i-convert-this-inner-join-with-multiple-conditions-to-dax-code-for-power[/url]