SQL to DAX Convert

[Anonymous]

Hello,

I'm new to DAX, please help to convert below T-SQL code to its equivalnet DAX.

I 'm generating running total (cummulative) using TSQL the same  i need in DAX, please help

select src.Final_date, sum([Units Assigned]) over(order by src.Final_date) as Final_cnt 
from (

select  [Received Date] as Final_date,   count(CARGO_ID) as [Units Assigned] from dbo.table1 where 
[Status]='ASSIGNED' and [Type]='TENDERED' and Dashboard='Inventory'
group by [Received Date]

) src
order by  src.Final_date desc

Thanks in advance

[v-yingjl]

Hi @Anonymous ,

Try to use this formula to create a calculate table, and use 'Sort descending' in the Data view:

A =
VAR tab =
    SUMMARIZE (
        FILTER (
            'dbo.table1',
            [Status] = "ASSIGNED"
                && [Type] = 'TENDERED'
                && [Dashboard] = 'Inventory'
        ),
        "Final_date", 'dbo.table1'[Received Date],
        "Units Assigned", COUNT ( 'dbo.table1'[CARGO_ID] )
    )
RETURN
    SELECTCOLUMNS (
        tab,
        "Final_cut", SUMX ( FILTER ( tab, [Final_date] <= MAX ( [Final_date] ) ), [Units Assigned] )
    )

Best Regards,
Yingjie Li

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

[Anonymous]

Thanks for your reply! . I'm having issue at this step:

EVALUATE 
	    SUMMARIZE (
        FILTER ( Inv_Fact_Inventory, 
						Inv_Fact_Inventory[Dashboard]="Current Inventory" && Inv_Fact_Inventory[Inventory Status]="ASSIGNED" && Inv_Fact_Inventory[Inventory Order Type]="TENDERED NOT SHIPPED" ),
,
        "Final_date", Inv_Fact_Inventory[Received Date],
        "Units Assigned", DISTINCTCOUNT(Inv_Fact_Inventory[CURINV_CARGO_ID])
    )

At Final_Date, below is the error

Next when i try to run the whole expression im getting the same value for all records.Am i missing something here..

Here is the updated code im using now:

EVALUATE 
	VAR tab =
    SUMMARIZE (
        FILTER (
             Inv_Fact_Inventory, 
						Inv_Fact_Inventory[Dashboard]="Current Inventory" && Inv_Fact_Inventory[Inventory Status]="ASSIGNED" && Inv_Fact_Inventory[Inventory Order Type]="TENDERED NOT SHIPPED" 
        ),
        Inv_Fact_Inventory[Received Date],
        "Units Assigned", DISTINCTCOUNT( Inv_Fact_Inventory[CURINV_CARGO_ID] )
    )
RETURN
    SELECTCOLUMNS (
        tab,
        "Final_cut", SUMX ( FILTER ( tab, Inv_Fact_Inventory[Received Date] <= MAX ( Inv_Fact_Inventory[Received Date] ) ), [Units Assigned] )
    )

Output:

Please advice

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Modify the formula like this:

EVALUATE 
	VAR tab =
	SUMMARIZE(
		FILTER(
			'Inv_Fact_Inventory',
			'Inv_Fact_Inventory'[Dashboard] = "Current Inventory" &&
			'Inv_Fact_Inventory'[Inventory Order Type] = "TENDERED" &&
			'Inv_Fact_Inventory'[Inventory Status] = "ASSIGNED"
		),
		'Inv_Fact_Inventory'[Received Date],
		"Units Assigned",
		 CALCULATE(COUNT(Inv_Fact_Inventory[CURINV_CARGO_ID]))
	)
RETURN
SELECTCOLUMNS(
	tab,
	"Final_Cut",
	var d = 'Inv_Fact_Inventory'[Received Date]
	return
	SUMX(
		FILTER(
			tab,
			[Received Date] <= d
		),
		[Units Assigned]
	)
)

order by [Final_Cut] desc

You may get your expected result, below is my sample and result:

Best Regards,
Yingjie Li

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

[Anonymous]

Your solution worked. Thanks for help..

[amitchandak]

@Anonymous , Try like

sumx(values(Table[Received Date]), calculate(count(Table[CARGO_ID]),filter(Table, table[Status]="ASSIGNED" && table[Type]="TENDERED" && table[Dashboard]="Inventory")))

[Anonymous]

Its not working.

I'm not getting values based on the formula that you have provided.