Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

SQL to DAX Convert

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     selec...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    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.

     

v-yingjl's avatar
v-yingjl
Community Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    v-yingjl
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Your solution worked. Thanks for help..