Forum Discussion

JBusque's avatar
JBusque
Frequent Visitor
1 year ago

Pulling Data from one table into a SummarizedColumns Table

Hello, I have a SummarizedColumns table that pulls from 3 seperate tables. I am working on getting related data from another table where it matches a ID, and has the Latest available date based on a specific paramater. Here is an example of the code as it is currently in DAX however it isn't running and is causing an issue where it says there isn't enough memory to complete the operation.

Latest SP Status Test =
VAR CID = CaseMaster[ID]
VAR WorkType = CaseMaster[WorkType]
VAR WorktypeStatus = Switch(
True(),
WorkType = "WorkType 1", CALCULATE(CALCULATE(MAX(Sharepoint[CaseStatus]),FILTER(Sharepoint,Sharepoint[Created Date] = CALCULATE(MAX(Sharepoint[Created Date]),Sharepoint[C ID] = CID && Sharepoint[CaseType] = "CaseType 1")))),
WorkType = "WorkType 2", CALCULATE(CALCULATE(MAX(Sharepoint[CaseStatus]),FILTER(Sharepoint,Sharepoint[Created Date] = CALCULATE(MAX(Sharepoint[Created Date]),Sharepoint[C ID] = CID && Sharepoint[CaseType] = "CaseType 2")))),
WorkType = "WorkType 3", CALCULATE(CALCULATE(MAX(Sharepoint[CaseStatus]),FILTER(Sharepoint,Sharepoint[Created Date] = CALCULATE(MAX(Sharepoint[Created Date]),Sharepoint[C ID] = CID && Sharepoint[CaseType] = "CaseType 3" || Sharepoint[CaseType] = "CaseType 4")))))
RETURN
Switch(
TRUE(),
ISBLANK(WorkTypeStatus), "Not Started",
NOT ISBLANK(WorkTypeStatus), WorkTypeStatus)

 

Ultimate goal is to get the latest "Status" from my SP table into my SummarizedColumns table based on its most recent date and if it has a matching ID and Work Type.

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi JBusque ,
    Based on your description and the code provided, an attempt is being made to get the latest status from the SharePoint table based on the latest date, matching ID and job type. You could try separating the calculation of the latest date into a variable (LatestDate) and then use that variable to find the appropriate status, avoiding the use of switches to overdo the matching.

    Latest SP Status Test =
VAR CID = CaseMaster[ID]
VAR WorkType = CaseMaster[WorkType]
VAR LatestDate = 
    CALCULATE(
        MAX(Sharepoint[Created Date]),
        FILTER(
            Sharepoint,
            Sharepoint[C ID] = CID &&
            (
                (WorkType = "WorkType 1" && Sharepoint[CaseType] = "CaseType 1") ||
                (WorkType = "WorkType 2" && Sharepoint[CaseType] = "CaseType 2") ||
                (WorkType = "WorkType 3" && (Sharepoint[CaseType] = "CaseType 3" || Sharepoint[CaseType] = "CaseType 4"))
            )
        )
    )
VAR WorktypeStatus = 
    CALCULATE(
        MAX(Sharepoint[CaseStatus]),
        FILTER(
            Sharepoint,
            Sharepoint[C ID] = CID &&
            Sharepoint[Created Date] = LatestDate &&
            (
                (WorkType = "WorkType 1" && Sharepoint[CaseType] = "CaseType 1") ||
                (WorkType = "WorkType 2" && Sharepoint[CaseType] = "CaseType 2") ||
                (WorkType = "WorkType 3" && (Sharepoint[CaseType] = "CaseType 3" || Sharepoint[CaseType] = "CaseType 4"))
            )
        )
    )
RETURN
IF(
    ISBLANK(WorktypeStatus),
    "Not Started",
    WorktypeStatus
)

     

    Best regards,
    Albert He

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