Forum Discussion

Jensej's avatar
Jensej
Helper V
5 years ago

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 bu...
Jensej's avatar
Jensej
Helper V
5 years ago

 

 

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

 

 

amitchandak's avatar
amitchandak
Super User
5 years ago

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

  • Jensej's avatar
    Jensej
    Helper V
    5 years ago

    Sadly still the same error 😞 

  • Jensej's avatar
    Jensej
    Helper V
    5 years ago

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