Dax Help For Self Join

[Anonymous]

Hello Experts,

I'm trying to create a measure from below TSQl that has self join on same table. 

could you please help me in getting its equivalent DAX code.

SELECT   
	COUNT(t1.[Cargo Id]) as SHIIPED_COUNT FROM
	
	EDW.Inv_Fact_Inventory t1 
JOIN 
	EDW.Inv_Fact_Inventory t2
ON 
	t1.[Cargo Id]=t2.[Cargo Id]
WHERE 
	t1.[Status Code]='SHIP' and t1.dashboard='Shipped Inventory'   
AND t1.[Ship Date] = t1.[Tender Date]
AND t1.[Ship Date] = CAST('2020-07-30' as date)
AND t2.[Status Code]='Tender'
AND t1.RAIL_HEAD_NUMBER = t2.RAIL_HEAD_NUMBER

I'm new to DAX , please advice.

[Greg_Deckler]

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Might try NATURALLEFTOUTERJOIN

[Anonymous]

Here is my table:

Cargo Id	Status Code	Tender Date	Ship Date	Location
3FAAAAAA12	TENDER	2020-07-09	Null	120
3N1CPAAA23	TENDER	2020-07-04	NULL	086
JN1BJ1CCCC	SHIP	2020-06-19	2020-06-22	005
3GKALPEEEE	TENDER	2020-07-30	NULL	01
3GKALPEEEE	SHIP	2020-07-30	2020-07-30	01
YS2R6XXXX	TENDER	2020-07-30	NULL	403
YS2R6XXXX	SHIP	2020-07-30	2020-07-30	403
3GNCJLLLLL	TENDER	2020-07-30	NULL	02
3GNCJLLLLL	SHIP	2020-07-30	2020-07-30	02

Here is the output i needed:

Count of Cargo Units that were Shipped and Tendered (Status as 'Ship' and 'Tender') on the same date (Ship Date is equal to Tender Date) from the same location.

So from the above sample table the last three cargo units matches the requirement, so it should return count of : 3

I use the below TSQL to achieve the output , but need some help to implement the same in DAX

SELECT   
	Count(t1.[cargo id])
	
	FROM
	
	EDW.Inv_Fact_Inventory t1 
JOIN 
	EDW.Inv_Fact_Inventory t2
ON 
	t1.[Cargo Id]=t2.[Cargo Id]
WHERE 
	t1.[Status Code]='SHIP'    
AND t1.[Ship Date] = t1.[Tender Date]
AND t1.[Ship Date] = CAST('2020-07-30' as date)
AND t2.[Status Code]='Tender'
AND t1.Location = t2.Location

[v-xuding-msft]

Hi @Anonymous ,

Please try this:

Measure =
COUNTROWS (
    FILTER (
        'Inv_Fact_Inventory',
        'Inv_Fact_Inventory'[Ship Date] = 'Inv_Fact_Inventory'[Tender Date]
            && 'Inv_Fact_Inventory'[Status Code] IN { "SHIP", "TENDER" }
    )
)

[Anonymous]

But how do i ensure that location for shipped and Tendered matches.

[v-xuding-msft]

Hi @Anonymous ,

Because the columns are from one table, for the same row, they are from the same location. We don't need to add other conditions. For example, For Id 3GKALPEEEE, data that meets the condition of equal date (2020/7/30) is just in the same location(1).

[Anonymous]

Business supports below condition:

Cargo unit can be tendered from one specific location say 01, but can be shipped from different location say 403 with different ship date.

So in that case i need additional filter just to make sure that location of Shipped and Tendered are same, apart from ship date and tender date. From sample data for CargoId: 3GNCJLLLLL need to make sure that Tendered Location and shipped location are also matching

 

[v-xuding-msft]

Hi @Anonymous ,

The second row doesn't meet the condition that Ship Date is equal to Tender Date. It will not be counted though their location is the same one.

If that formula can't work in your actual report, you could try this one. I add a condition of location.

Measure = 
COUNTROWS (
    FILTER (
        'Inv_Fact_Inventory',
        'Inv_Fact_Inventory'[Ship Date] = 'Inv_Fact_Inventory'[Tender Date]
            && 'Inv_Fact_Inventory'[Status Code] IN { "SHIP", "TENDER" }
            && Inv_Fact_Inventory[Location] = MAX(Inv_Fact_Inventory[Location])
    )
)

[Anonymous]

By applying this filter , its not returning any result

Inv_Fact_Inventory[Location] = MAX(Inv_Fact_Inventory[Location])

[richbenmintz]

Hi @Anonymous,

The following formula will identify the matches, then you can create a measure to sum up the column, One thing i modified in your data was replacing null ship dates with '1900-01-01'

Match Count = 
var shipDate = [Ship Date]
var statusCode = [Status Code]
var location = [Location]
return 
CALCULATE(COUNTROWS('Table'), FILTER(ALL('Table'), 'Table'[Tender Date] = shipDate && 'Table'[Status Code] = "Tender" && 'Table'[Location] = location))

I hope this helps

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

Just one query:

How these variables are getting values, i'm not able to understand from the below declaration

var shipDate = [Ship Date]
var statusCode = [Status Code]
var location = [Location]

Please calrify

[richbenmintz · Accepted solution]

The Variables set the value to be used in the filter part of the calculate in the context of the row being evalulated

So essentially what the calc column is doing is saying count the number of rows in the entire table where the location = location in the current row and the status = the status in the current row and the tender date = the ship date in the current row.

Make Sense?

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

[Anonymous]

it would be useful to see the model in which you're trying to create this measure