Forum Discussion

Spencer's avatar
Spencer
Helper II
9 years ago
Solved

Relationships & Visuals

Hi   In the report I'm creating I have the amount of new leavers and starters for each month. When I select a particular month on a bar chart for example, I'd like for a table on the report to sho...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Spencer,

    Based on your description, I have some misunderstanding of your table structure(I store the name to the ‘leavers’ and ‘starters’).
     
    Since you store the count value of records into the ‘leavers’ and ‘starters’, you can use CONCATENATEX function to achieve your requirement, below is a sample:

    Create a simple table with deptcode, monthjoin, monthleave, name


     

    Use dax table formula to create two table DeptST,DeptLE

    DeptST = DISTINCT( SELECTCOLUMNS(Sheet1,"DeptCode",[DeptCode],"MonthJoined",[MonthJoined],"Starter",COUNTROWS(FILTER(Sheet1,Sheet1[DeptCode]=EARLIER(Sheet1[DeptCode]) && Sheet1[MonthJoined]=EARLIER(Sheet1[MonthJoined])))))


     

    DeptLE = DISTINCT( SELECTCOLUMNS(Sheet1,"DeptCode",[DeptCode],"MonthLeft",[MonthLeft],"Leaver",COUNTROWS(FILTER(Sheet1,Sheet1[DeptCode]=EARLIER(Sheet1[DeptCode]) && Sheet1[MonthLeft]=EARLIER(Sheet1[MonthLeft])))))


    Write measure to get the specify name which in deptST and deptLE.

    Detail of Starter = var joindate= MAX([MonthJoined]) return

    CONCATENATEX(FILTER(Sheet1,Sheet1[DeptCode]=VALUES(DeptST[DeptCode])&& Sheet1[MonthJoined]=joindate),[Name]&",")

     

    Detail of Leaver = var leavedate= MAX([MonthLeft]) return

    CONCATENATEX(FILTER(Sheet1,Sheet1[DeptCode]=VALUES(DeptLE[DeptCode])&& Sheet1[MonthLeft]=leavedate),[Name]&",")
     

    Add calculate columns to store and display them.

     

    Regards,

    Xiaoxin Sheng

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Spencer,

 

>>Is there anyway to have those appear when slicing?

You could drag the name column to the table visuals.

 

Relationship:

 

 

Table visual structure:

 

 

Result:

 

 

Regards,

Xiaoxin Sheng

Spencer's avatar
Spencer
Helper II
9 years ago

Hi Xiaoxin Sheng,

Thanks again for the response and apoligies for my slow reply. I wasn't able to create the relationships between the Employee Master and Dept tables as per your suggestion earlier.

I have since been able to reduce the number of tables in my model by creating measures that calculate the leavers and starters directly from the Employee Master table using the following functions:

Leavers = CALCULATE(COUNTROWS('Employee Master'),FILTER('Employee Master',([termination_date] <= LASTDATE(DateTable[DateKey]) && [termination_date]>=FIRSTDATE(DateTable[DateKey]))))

Starters = CALCULATE(COUNTROWS('Employee Master'),FILTER('Employee Master',([commence_date] <= LASTDATE(DateTable[DateKey]) && [commence_date]>=FIRSTDATE(DateTable[DateKey]))))

 

Unfortunately this still won't give me the names of the particular employees when I filter on the visual (see screenshot). How would you solve this under my revised set up? (I have tried activating a relationship between DateTable and Employee Master but haven't been able to achieve my desired result)

Huge thanks,

Spencer

RelationshipsIntended Visual

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Spencer,

    Based on your description, I have some misunderstanding of your table structure(I store the name to the ‘leavers’ and ‘starters’).
     
    Since you store the count value of records into the ‘leavers’ and ‘starters’, you can use CONCATENATEX function to achieve your requirement, below is a sample:

    Create a simple table with deptcode, monthjoin, monthleave, name


     

    Use dax table formula to create two table DeptST,DeptLE

    DeptST = DISTINCT( SELECTCOLUMNS(Sheet1,"DeptCode",[DeptCode],"MonthJoined",[MonthJoined],"Starter",COUNTROWS(FILTER(Sheet1,Sheet1[DeptCode]=EARLIER(Sheet1[DeptCode]) && Sheet1[MonthJoined]=EARLIER(Sheet1[MonthJoined])))))


     

    DeptLE = DISTINCT( SELECTCOLUMNS(Sheet1,"DeptCode",[DeptCode],"MonthLeft",[MonthLeft],"Leaver",COUNTROWS(FILTER(Sheet1,Sheet1[DeptCode]=EARLIER(Sheet1[DeptCode]) && Sheet1[MonthLeft]=EARLIER(Sheet1[MonthLeft])))))


    Write measure to get the specify name which in deptST and deptLE.

    Detail of Starter = var joindate= MAX([MonthJoined]) return

    CONCATENATEX(FILTER(Sheet1,Sheet1[DeptCode]=VALUES(DeptST[DeptCode])&& Sheet1[MonthJoined]=joindate),[Name]&",")

     

    Detail of Leaver = var leavedate= MAX([MonthLeft]) return

    CONCATENATEX(FILTER(Sheet1,Sheet1[DeptCode]=VALUES(DeptLE[DeptCode])&& Sheet1[MonthLeft]=leavedate),[Name]&",")
     

    Add calculate columns to store and display them.

     

    Regards,

    Xiaoxin Sheng

     

    • Spencer's avatar
      Spencer
      Helper II
      9 years ago

      Got it now, thanks for all you help!