topic Re: SQL into Dax in DAX Commands and Tips

SQL into Dax

Jensej — Mon, 15 Feb 2021 09:11:14 GMT

Hello!

I have a table in Power Bi with a Month Column Jan-Dec for year 2020. Now i want to a second column that shows the people who resigned in that month.

In sql i would solve it like this but i don't know if it would work if just create a measure and i don't know the dax code for it.

Can someone help with the dax code for this select?

SELECT Name + CAST(day(Resign_Date) as varchar) as Name FROM BI_Adr WHERE Entry_Date <= GETDATE() and Resign_Date > GETDATE()

Re: SQL into Dax

amitchandak — Mon, 15 Feb 2021 09:24:50 GMT

@Jensej , In a table visual take name, Resign_Date and this measure

measure =calculate(countrows(Table), filter(Table, Table[Entry_Date] <=Today() && Table[Resign_Date] >=Today()))

Re: SQL into Dax

Jensej — Mon, 15 Feb 2021 10:21:17 GMT

HI @amitchandak

Thanks for your reply, if i do this i get this error message in the table visual. "A table of multiple values was supplied where a single value was expected"

Re: SQL into Dax

amitchandak — Mon, 15 Feb 2021 10:25:36 GMT

@Jensej , Can you share your measure

Re: SQL into Dax

Jensej — Mon, 15 Feb 2021 11:40:13 GMT

Measure 2 = VAR first= min(JF_BI_Datum[Datum]) VAR last= max(JF_BI_Datum[Datum]) RETURN calculate(countrows(JF_BI_Adr), filter(JF_BI_Adr,DATESBETWEEN(JF_BI_Adr[Resign_Date],first,last)))

Re: SQL into Dax

amitchandak — Mon, 15 Feb 2021 12:49:29 GMT

@Jensej , Try like

Measure 2 =
VAR first= minX(allselected(JF_BI_Datum), JF_BI_Datum[Datum])
VAR last= maxX(allselected(JF_BI_Datum),JF_BI_Datum[Datum])

RETURN
calculate(countrows(JF_BI_Adr), filter(JF_BI_Adr,DATESBETWEEN(JF_BI_Adr[Resign_Date],first,last)))

Re: SQL into Dax

Jensej — Mon, 15 Feb 2021 13:17:56 GMT

Sadly still the same error 😞

Re: SQL into Dax

Jensej — Tue, 16 Feb 2021 08:47:22 GMT

HI @amitchandak

I almost solved the problem with this Measure. It shows me the day and name of the person that left that month. It works perfect as long as only one person left that month but if it's more it only shows 1 person. Is there some way to do some kind of loop and see if there is more persons and then add like a comma and that person on the same row?

Resigns = CALCULATE ( DAY(min(JF_BI_Adr[Resign_Date])) & " " & min(JF_BI_Adr[Full Name]), USERELATIONSHIP (JF_BI_Adr[Resign_Date], JF_BI_Datum[Datum] ))