Forum Discussion

RUExperienc3d's avatar
RUExperienc3d
Regular Visitor
2 years ago
Solved

How to make a column relationship conditional based on data in another column

Hi Community!

 

I have a table with relational date slicers (i.e. last week, last month, etc.) that has two relationships with a primary data table called SecurityIncidents, which is a Microsoft Sentinel table built on a Log Workspace. The table I need to relate is external and has low fildelity data identifiers and sometimes, may have duplicates that should be aggregated but only if they are in the same date. How can I make that table relationship coniditional on another column match such as date?

 

For example, the following table has no primary identifier with the SecurityIncidents table in my log workspace, because in this dataset, the column of ID is an external number. The only data that can be related is the column for Summary, for which is not unique and may have occurred at other dates. There is the Time Created (I have created a DateCreated column for simplicity sake of only date) but it is not unique, since other Summary of alerts in rows happen that do not have the same Summary or content, but have the same date. 

 

External Table (the only two columns that we can match on in the SecurityIncidents are Summary and TimeCreated)

Severity    ID                   Summary                                      Count Category             Status                           Time Created

LowMSS0138052Alert for suspicious activity No1118CollectionResolved by customer2024-02-19T21:03:34UTC
MediumMSS0135931Alert for suspicious activity No21Defense EvasionResolved by customer2024-02-07T15:17:06UTC
HighMSS0134928Alert for suspicious activity No31ExecutionResolved by customer2024-02-01T21:33:46UTC
Data Protection
Need Help
Tips and Tricks
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Hello RUExperienc3d,

     

    Can you please try the following:

     

    1. Ensure Date Consistency (For both tables, you could create a calculated column like this)

    DateCreated = DATEVALUE(Format([Time Created], "YYYY-MM-DD"))

    2. Create a Composite Key (For both the external table and the SecurityIncidents table, add a new calculated column)

    CompositeKey = [Summary] & " | " & FORMAT([DateCreated], "YYYY-MM-DD")

    3. Use DAX for Conditional Relationships

    Aggregated Measure = 
SUMX(
    FILTER(
        ExternalTable,
        CONTAINS(SecurityIncidents, SecurityIncidents[CompositeKey], ExternalTable[CompositeKey])
    ),
    ExternalTable[SomeValueColumn] // Replace
)

    4. Addressing Duplicates

    Aggregated External Table = 
SUMMARIZE(
    ExternalTable,
    [CompositeKey],
    "AggregatedValue", SUM(ExternalTable[SomeValueColumn]) // Adjust as needed
)

    Hope this helps!

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    Hello RUExperienc3d,

     

    Can you please try the following:

     

    1. Ensure Date Consistency (For both tables, you could create a calculated column like this)

    DateCreated = DATEVALUE(Format([Time Created], "YYYY-MM-DD"))

    2. Create a Composite Key (For both the external table and the SecurityIncidents table, add a new calculated column)

    CompositeKey = [Summary] & " | " & FORMAT([DateCreated], "YYYY-MM-DD")

    3. Use DAX for Conditional Relationships

    Aggregated Measure = 
SUMX(
    FILTER(
        ExternalTable,
        CONTAINS(SecurityIncidents, SecurityIncidents[CompositeKey], ExternalTable[CompositeKey])
    ),
    ExternalTable[SomeValueColumn] // Replace
)

    4. Addressing Duplicates

    Aggregated External Table = 
SUMMARIZE(
    ExternalTable,
    [CompositeKey],
    "AggregatedValue", SUM(ExternalTable[SomeValueColumn]) // Adjust as needed
)

    Hope this helps!