Need DAX help

[azaterol]

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?

[v-xiaotang · Accepted solution]

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.

[amitchandak]

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