SQL to DAX

[Anonymous]

Dear Community Members!



I have two SQL scripts and I would like to transform into DAX codes but it don't work for me either:



I have three different id: 0,1,2

1.

Full income costs:

=[fix basic costs] + If [extra costs]>=Sum(income costs) Where ([id]<>2;[id]) Then [extra costs]+Sum(income costs) Where ([id]=2;[id]) Else Sum([income costs])

My version:

Full income costs =

Actual[basic costs] + IF((Actual[extra costs] >= sum(Actual[income costs]) && Actual[id] <> 2); IF(Actual[id] = 2; (Actual[extra costs] + sum(Actual[income costs])); sum(Actual[income costs])); Actual[income costs])

2.

Full income costs with discounts:

=If Sum([income costs)])=0 or Sum([operational costs])=0 Then 0 Else [full income costs] - Sum([operational costs]) - [specific costs] - [discount cost]

My version:

Full income costs with discounts =

IF(sum(Actual[income costs]) = 0 || sum(Actual[operational costs]) = 0; 0; (Actual[income costs]-Actual[operational costs]-Actual[specific costs])-Actual[doscounts cost])

Thank you for you efforts,

[v-jiascu-msft]

Hi @Anonymous,

Can you share a sample? A dax formula has tight connections to the data model structure. Can you post the expected result? I'm afraid the first pseudo-code isn't clear. It could be like below.

measure 1 =
[fix basic costs]
    + IF (
        MIN ( actual[extra costs] ) >= SUM ( actual[income costs] )
            && MIN ( actual[id] ) <> 2;
        MIN ( actual[extra costs] ) + SUM ( actual[income costs] );
        IF (
            MIN ( actual[id] ) = 2;
            MIN ( actual[extra costs] ) + SUM ( actual[income costs] );
            SUM ( actual[income costs] )
        )
    )

Best Regards,

Dale

[Anonymous]

[Anonymous]

Dear All,

I would like to correct and clarify the final result so I sent a new table which contain that column what I want to get with the two above SQL script.

Please help me to tranfer those SQL scripts into DAX codes.

Thank you for everyone.

Regards,

Attila

[Anonymous]

[Anonymous]

Dear @v-jiascu-msft,

Firstly I really thank you your answer, secondly here is the sample which show my desired result.

I think the DAX code should work on Company_id level to count the proper number.

 Best regards,

Attila