Forum Discussion

Jensej's avatar
Jensej
Icon for Helper V rankHelper 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
Icon for Helper V rankHelper 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
    Icon for Super User rankSuper 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
      Icon for Helper V rankHelper V
      5 years ago

      Sadly still the same error 😞 

    • Jensej's avatar
      Jensej
      Icon for Helper V rankHelper 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] ))