Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply

Convert SQL Query to DAX

Hey,

 

I have this table at Power BI (IdsSemHistorico)

 

SELECT P.Id
FROM Pessoas P
WHERE NOT EXISTS (
    SELECT 1
    FROM Pessoas_Historico PH
    WHERE PH.IdPessoa = P.Id
        AND PH.Ativo IS NOT NULL
    )

 

and then I need a Measure in DAX that give me some calculations. The SQL Query is the following:

 

SELECT COUNT(*)
FROM Pessoas P
LEFT JOIN IdsSemHistorico I ON P.Id = I.Id
WHERE ISNULL(P.DataAdmissao, P.StampData) <= @MonthStart
    AND ISNULL(P.DataRescisao, @MonthEnd) <= @MonthEnd
    AND (
        (I.Id IS NOT NULL AND P.activo = 'S')
        OR (
            I.Id IS NULL
            AND ISNULL((
                    SELECT TOP 1 PH.Ativo
                    FROM Pessoas_Historico PH
                    WHERE PH.IdPessoa = P.Id
                        AND PH.DataInicioGrupo <= @MonthStart
                        AND PH.Ativo IS NOT NULL
                    ORDER BY PH.DataInicioGrupo DESC
                    ), 1) = 1
            )
        )

 

 Can anyone help me transform this second query at a DAX expression?

1 ACCEPTED SOLUTION
AmiraBedh
Super User
Super User

While I don't recommend this approach but here is my attempt :

EmployeeCount =
VAR MonthStart = DATEVALUE("YYYY-MM-DD") -- Replace with your @MonthStart logic
VAR MonthEnd = DATEVALUE("YYYY-MM-DD") -- Replace with your @MonthEnd logic
RETURN
CALCULATE(
COUNTROWS(Pessoas),
FILTER(
Pessoas,
ISBLANK(Pessoas.DataAdmissao) || Pessoas.DataAdmissao <= MonthStart
),
FILTER(
Pessoas,
ISBLANK(Pessoas.DataRescisao) || Pessoas.DataRescisao <= MonthEnd
),
FILTER(
Pessoas,
Pessoas.Activo = "S"
|| (NOT(ISBLANK(Pessoas.Id)) &&
CALCULATE(
COUNTROWS(Pessoas_Historico),
FILTER(
Pessoas_Historico,
Pessoas_Historico.IdPessoa = Pessoas.Id
&& Pessoas_Historico.DataInicioGrupo <= MonthStart
&& NOT(ISBLANK(Pessoas_Historico.Ativo))
),
ALL(Pessoas_Historico),
TOPN(
1,
FILTER(
Pessoas_Historico,
Pessoas_Historico.IdPessoa = Pessoas.Id
&& Pessoas_Historico.DataInicioGrupo <= MonthStart
&& NOT(ISBLANK(Pessoas_Historico.Ativo))
),
Pessoas_Historico.DataInicioGrupo,
DESC
)
) > 0
)
)
)

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

1 REPLY 1
AmiraBedh
Super User
Super User

While I don't recommend this approach but here is my attempt :

EmployeeCount =
VAR MonthStart = DATEVALUE("YYYY-MM-DD") -- Replace with your @MonthStart logic
VAR MonthEnd = DATEVALUE("YYYY-MM-DD") -- Replace with your @MonthEnd logic
RETURN
CALCULATE(
COUNTROWS(Pessoas),
FILTER(
Pessoas,
ISBLANK(Pessoas.DataAdmissao) || Pessoas.DataAdmissao <= MonthStart
),
FILTER(
Pessoas,
ISBLANK(Pessoas.DataRescisao) || Pessoas.DataRescisao <= MonthEnd
),
FILTER(
Pessoas,
Pessoas.Activo = "S"
|| (NOT(ISBLANK(Pessoas.Id)) &&
CALCULATE(
COUNTROWS(Pessoas_Historico),
FILTER(
Pessoas_Historico,
Pessoas_Historico.IdPessoa = Pessoas.Id
&& Pessoas_Historico.DataInicioGrupo <= MonthStart
&& NOT(ISBLANK(Pessoas_Historico.Ativo))
),
ALL(Pessoas_Historico),
TOPN(
1,
FILTER(
Pessoas_Historico,
Pessoas_Historico.IdPessoa = Pessoas.Id
&& Pessoas_Historico.DataInicioGrupo <= MonthStart
&& NOT(ISBLANK(Pessoas_Historico.Ativo))
),
Pessoas_Historico.DataInicioGrupo,
DESC
)
) > 0
)
)
)

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors