Forum Discussion

dk_powerbiuser1's avatar
dk_powerbiuser1
New Member
2 years ago
Solved

Fetch column data from unrelated table using DAX

Need help for writing DAX 

I have 3 tables Survey , Employee and Target

 

A weekly snapshot data is available in Employee table. So each month we have 4 or 5 weeks data 

 

 

 

Requirement is  to take latest weekly snapshot EmployeeID count for each month and corresponding Month survey count from the Survey table (Complete one month count) who ever is part of this latest Weekly snapshot (only matching record count based on Employee_ID column and Month of (Date columns) 

 

No Active Relationship between these tables

 

Based on the above sample data, Survery % for Jan = 1/6 (we have only 1 survey participation in Survey Table , 6 EmployeeIDs in14th Jan 2024    

 

Survery % for Feb = 4/7 (we have only 4 survey participation in Survey Table(matching records) , 6 EmployeeIDs in 25th Feb 2024

 

We should also find the Target value based on no. of days of each month and target value from target table .Example Jan Target =31* .50 which is extracted from Target table (Select Target from Targets where metric="Survey" and TArget Type="Daily") 

 

We need to Plot the Line& clustered column chart (x axis : Survey_Date, column Y Axis: Survey %, Line Y Axis :Target  )

 

Thank you in Advance

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi dk_powerbiuser1 ，

    For your needs, since it operates on a pound per month basis, we made some changes in the date column.

     

    MonthYear = FORMAT(Employee[Date],"YYYY-MM")

     

    As per the requirement, we want to get the latest snapshot of each month, which in DAX is also the maximum value.

     

    LatestSnapshotCount = 
VAR LatestDates =
   ADDCOLUMNS(
       SUMMARIZE(
           Employee,
           Employee[EmployeeID],
           Employee[MonthYear],
           "MaxDate", MAX(Employee[Date])
       ),
       "Year", YEAR([MaxDate]),
       "Month", MONTH([MaxDate])
   )
RETURN
CALCULATE (
   COUNTROWS (
       SUMMARIZE (
           FILTER (
               Employee,
               Employee[Date] IN SELECTCOLUMNS(LatestDates, "MaxDate", [MaxDate])
           ),
           Employee[EmployeeID]
       )
   )
)

     

    Then calculate how much of the data in the SURVEY table matches the data in the EMPLOYEE table.

     

    MonthlySurveyCount = 
VAR CurrentYear = YEAR(MAX(Employee[Date]))
VAR CurrentMonth = MONTH(MAX(Employee[Date]))
RETURN
CALCULATE (
   COUNT(Survey[Employee_ID]),
   YEAR(Survey[Survey_Date]) = CurrentYear,
   MONTH(Survey[Survey_Date]) = CurrentMonth
)

     

    Calculate the required percentage.

     

    SurveyPercentage = 
DIVIDE (
   [MonthlySurveyCount],
   [LatestSnapshotCount]
)

     

    Your last requirement, which is the need to have a target value.

     

    TargetValue = 
VAR DaysInMonth = DAY ( EOMONTH ( MAX(Employee[Date]), 0 ) )
VAR DailyTarget =
CALCULATE (
VALUES ( Target[Target] ),
Target[Metric] = "Survey",
Target[TargetType] = "Daily" )
RETURN
DaysInMonth * DailyTarget

     

    And finally, we have the bar and line chart we need.

    If you still have questions, check out my example pbix, which will make it a little clearer for you.

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

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

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi dk_powerbiuser1 ，

    For your needs, since it operates on a pound per month basis, we made some changes in the date column.

     

    MonthYear = FORMAT(Employee[Date],"YYYY-MM")

     

    As per the requirement, we want to get the latest snapshot of each month, which in DAX is also the maximum value.

     

    LatestSnapshotCount = 
VAR LatestDates =
   ADDCOLUMNS(
       SUMMARIZE(
           Employee,
           Employee[EmployeeID],
           Employee[MonthYear],
           "MaxDate", MAX(Employee[Date])
       ),
       "Year", YEAR([MaxDate]),
       "Month", MONTH([MaxDate])
   )
RETURN
CALCULATE (
   COUNTROWS (
       SUMMARIZE (
           FILTER (
               Employee,
               Employee[Date] IN SELECTCOLUMNS(LatestDates, "MaxDate", [MaxDate])
           ),
           Employee[EmployeeID]
       )
   )
)

     

    Then calculate how much of the data in the SURVEY table matches the data in the EMPLOYEE table.

     

    MonthlySurveyCount = 
VAR CurrentYear = YEAR(MAX(Employee[Date]))
VAR CurrentMonth = MONTH(MAX(Employee[Date]))
RETURN
CALCULATE (
   COUNT(Survey[Employee_ID]),
   YEAR(Survey[Survey_Date]) = CurrentYear,
   MONTH(Survey[Survey_Date]) = CurrentMonth
)

     

    Calculate the required percentage.

     

    SurveyPercentage = 
DIVIDE (
   [MonthlySurveyCount],
   [LatestSnapshotCount]
)

     

    Your last requirement, which is the need to have a target value.

     

    TargetValue = 
VAR DaysInMonth = DAY ( EOMONTH ( MAX(Employee[Date]), 0 ) )
VAR DailyTarget =
CALCULATE (
VALUES ( Target[Target] ),
Target[Metric] = "Survey",
Target[TargetType] = "Daily" )
RETURN
DaysInMonth * DailyTarget

     

    And finally, we have the bar and line chart we need.

    If you still have questions, check out my example pbix, which will make it a little clearer for you.

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

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