Forum Discussion

Rune_'s avatar
Rune_
Frequent Visitor
2 years ago
Solved

Dax formula that does not filter on calender table

I need to know how my DAX formula called "Fakturert" needs to be updated to filter on dimKalender. 

 

I have 3 tables 

Relationship between 
JobTask[Job No_] -->[JobLedgerEntry[Job No_]
dimKalender[Date] -->[JobLedgerEntry[Posting Date]

 

In Power Bi report view in my table where I use JobTask[Job No_] 

When I use dimKalender for filter it only filters correctly for one of my DAX formulas. 

 

Incorrectly for this Dax
Fakturert =
SUMX(
    FILTER(
        JobTask,
        JobTask[Job Task Type] = 4
    ),
    CALCULATE(
        SUM(JobLedgerEntry[Line Amount (LCY)]),
        FILTER(
            ALL(JobLedgerEntry),
            JobLedgerEntry[Job No_] = EARLIER(JobTask[Job No_]) &&
            JobLedgerEntry[Job Task No_] >= EARLIER(JobTask[Totaling.1]) &&
            JobLedgerEntry[Job Task No_] <= EARLIER(JobTask[Totaling.2]) &&
            JobLedgerEntry[Source Code] = "SALG"
        )
    )
)
 
Correct for this DAX
FakturertLinje =
VAR SelectedJobNo = SELECTEDVALUE(JobTask[Job No_])
VAR SelectedJobTaskNo = SELECTEDVALUE(JobTask[Job Task No_])

RETURN
CALCULATE(
        SUMX(
        FILTER(
            JobLedgerEntry,
            JobLedgerEntry[Job No_] = SelectedJobNo &&
            JobLedgerEntry[Job Task No_] = SelectedJobTaskNo &&
            JobLedgerEntry[Source Code] = "SALG"
        ),
        JobLedgerEntry[Line Amount (LCY)]
        )
    )
 Dataset
Filter
relationships
  • Rune_'s avatar
    Rune_
    2 years ago

    Solutions

    Fakturert =
    -SUMX(
        FILTER(
            JobTask,
            JobTask[Job Task Type] = 4
        ),
        CALCULATE(
            SUM(JobLedgerEntry[Line Amount (LCY)]),
            FILTER(SUMMARIZE(
                JobLedgerEntry,
                JobLedgerEntry[Job No_],
                JobLedgerEntry[Job Task No_],
                JobLedgerEntry[Posting Date],
                JobLedgerEntry[Source Code]
            ),
                JobLedgerEntry[Job No_] = EARLIER(JobTask[Job No_]) &&
                JobLedgerEntry[Job Task No_] >= EARLIER(JobTask[Totaling.1]) &&
                JobLedgerEntry[Job Task No_] <= EARLIER(JobTask[Totaling.2]) &&
                JobLedgerEntry[Source Code] = "SALG"
            )
        )
    )

1 Reply

  • Rune_'s avatar
    Rune_
    Frequent Visitor
    2 years ago

    Solutions

    Fakturert =
    -SUMX(
        FILTER(
            JobTask,
            JobTask[Job Task Type] = 4
        ),
        CALCULATE(
            SUM(JobLedgerEntry[Line Amount (LCY)]),
            FILTER(SUMMARIZE(
                JobLedgerEntry,
                JobLedgerEntry[Job No_],
                JobLedgerEntry[Job Task No_],
                JobLedgerEntry[Posting Date],
                JobLedgerEntry[Source Code]
            ),
                JobLedgerEntry[Job No_] = EARLIER(JobTask[Job No_]) &&
                JobLedgerEntry[Job Task No_] >= EARLIER(JobTask[Totaling.1]) &&
                JobLedgerEntry[Job Task No_] <= EARLIER(JobTask[Totaling.2]) &&
                JobLedgerEntry[Source Code] = "SALG"
            )
        )
    )