Forum Discussion
Dax Help For Self Join
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.
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!
12 Replies
- Greg_Deckler
Community Champion
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
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
richbenmintzResident Rockstar
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!
