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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Jensej
Helper V
Helper V

SQL into Dax

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()

 

7 REPLIES 7
amitchandak
Super User
Super User

@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()))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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"

@Jensej , Can you share your measure

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

 

 

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)))

 

 

@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)))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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] ))

 

Sadly still the same error 😞 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.