Forum Discussion

azaterol's avatar
azaterol
Helper V
4 years ago
Solved

Need DAX help

Hey,

 

I wrote SQL script but I want it also in Power BI.

The SQL is 

 

Select Distinct DocNr, Salary
FROM DocPosition
JOIN Document ON DocPosition.PosNr = Document.DocNr
WHERE DocDate Between '01.04.2022' and '30.04.2022' and DocPosition.Nr = 4300 and DocArt =3

 

My idea is:

Doc Salary= CALCULATE(DISTINCT(Document[DocNr]),SUM(Document[Salary]))
 
But it wont work.
 
You have an idea how I can transport it to DAX language?

 

 

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi azaterol 

    Thanks for reaching out to us.

    >> to  transport it to DAX language

    You can try this,

    Table = 
var _t1= SELECTCOLUMNS(FILTER(Table_1,Table_1[Nr]=4300),"PosNr",[PosNr])
var _t2= SELECTCOLUMNS(FILTER(Table_2,Table_2[DocDate]<=DATE(2022,4,30) && Table_2[DocDate]>=DATE(2022,4,1) && Table_2[DocArt]=3 && Table_2[DocNr] in _t1),"DocNr",[DocNr],"Salary",[Salary])
return _t2

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    azaterol , In power bi You will take Document[DocNr] as unsummarized measure and you can use a measure

    SUM(Document[Salary])

     

    Filter can used using filter of slicer or you can use measure filter

     

    example

     

    calculate(SUM(Document[Salary]), filter(Document, DocDate >= date(2022,04,01) && DocDate < = date(2022,04,30) && [DocPosition.Nr] = 4300 && [DocArt] =3 ))

     

     

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi azaterol 

    Thanks for reaching out to us.

    >> to  transport it to DAX language

    You can try this,

    Table = 
var _t1= SELECTCOLUMNS(FILTER(Table_1,Table_1[Nr]=4300),"PosNr",[PosNr])
var _t2= SELECTCOLUMNS(FILTER(Table_2,Table_2[DocDate]<=DATE(2022,4,30) && Table_2[DocDate]>=DATE(2022,4,1) && Table_2[DocArt]=3 && Table_2[DocNr] in _t1),"DocNr",[DocNr],"Salary",[Salary])
return _t2

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.