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

 

 

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

 

 

 

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.

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

      Its not working.

       

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

  • 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.