Forum Discussion

rossjmccarthy's avatar
rossjmccarthy
New Member
5 years ago
Solved

Latest date across multiple tables

Hi all,

 

Apologies for this newbie question but I could not find the answer through a lo of searching on this.

 

I am building a KPI report which is pulling data from multiple databases such as Anti-Virus, Patching, Encryption and other systems

The relationships are all in place, using computer host names.

 

What I need to do is find the latest data a compute was seen online.

For each system I have a record holding a date on which each computer last checked into it.

I need to grap all these dates, across the multiple sources, and populate a field in the main table called "LastOnline"

 

Sources would be similiar to:

ePOLeafNode[LastSyncDate]

FalconData[LastCheckinDate]

ActiveDirectory{LastOnlineTimestamp]

etc

 

This is probably very easy but the only way I can do this currently is by merging tables and this is a mess.

 

Any help much appreciated!

Ross.

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, rossjmccarthy ;

    According to your description, I create a simple example, and you could create a measure or new table by dax.

    Create a measure

    Measure = 
var _a=MAX('ActiveDirectory'[LastOnlineTimestamp])
var _b=MAX('ePOLeafNode'[LastSyncDate])
var _c=MAX('FalconData'[LastCheckinDate])
return MAX(_c,MAX(_a,_b))

    The final output is shown below:

     

    or create a new table.

    newTable =
var _a=SUMMARIZE('ActiveDirectory',[LastOnlineTimestamp])
var _b=SUMMARIZE('ePOLeafNode',[LastSyncDate])
var _c=SUMMARIZE('FalconData',[LastCheckinDate])
return UNION(_a,_b,_c)

     The final output is shown below:

    If it's not right, Could you please consdier sharing more details about it or a simple sample file without any sesentive information?

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You have to show the model.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, rossjmccarthy ;

    According to your description, I create a simple example, and you could create a measure or new table by dax.

    Create a measure

    Measure = 
var _a=MAX('ActiveDirectory'[LastOnlineTimestamp])
var _b=MAX('ePOLeafNode'[LastSyncDate])
var _c=MAX('FalconData'[LastCheckinDate])
return MAX(_c,MAX(_a,_b))

    The final output is shown below:

     

    or create a new table.

    newTable =
var _a=SUMMARIZE('ActiveDirectory',[LastOnlineTimestamp])
var _b=SUMMARIZE('ePOLeafNode',[LastSyncDate])
var _c=SUMMARIZE('FalconData',[LastCheckinDate])
return UNION(_a,_b,_c)

     The final output is shown below:

    If it's not right, Could you please consdier sharing more details about it or a simple sample file without any sesentive information?

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.