Creating a measure that counts the rows if a particular column is greater or equal of a certain date

[KamEt69]

Hello!

I am in a peculiar situation: I have a fact table that I am not even sure that fits the definition of "Fact table".

this table is a load log for an etl proces, basically a list of other tables and their statuses.

we can imagine it with 3 columns:

• DateModified -> pretty self explanatory
• Watermark -> this column will have the date refering to the freshness of data (if the load succeded the day 08/05/2023 than this column will contain 07/05/2023)
• TableName -> also pretty self explanatory

I also have a date table witch I have connected to a 1 to many relationship through the column date modified.

It also can happen that the load of a table fails and it will have date modified 08/05/2023 but watermark still at 06/05/2023.

I have created a measure that counts how many tables are loaded but it works strange, because if there are 50 tables ready for today  and 50 table still to load, so with yesterday's watermark, the simple count will show 50 for today, 50 for yesterday and blank for allo the previous days.

I would like instead to create a metric that when it calculates the number of table loaded for a set day will keep in consideration that if a table is loaded with watermark 07/05/2023 it means that is loaded also for all the days before.

I have tryed to achieve this with a filter like following:

NrTablesLoaded = countx(filter(myTable, myTable[Watermark] >= dayselected), myTable[Watermark] )

Of course the measure does not work.

Can please somebody help?

[Muhammad110 · Accepted solution]

I get what you're trying to do. You want to create a measure that counts the number of tables loaded for a specific day, considering the watermark dates. However, the measure you're using with the COUNTX function and filtering on the watermark column isn't giving you the results you want.

To make it work the way you want, you can tweak your measure like this: 

NrTablesLoaded =
CALCULATE(
COUNTROWS(myTable),
FILTER(
ALL(myTable),
myTable[Watermark] <= MAX(myTable[Watermark]) &&
myTable[DateModified] <= MAX(DateTable[Date])
)
)

[Muhammad110 · Accepted solution]

I get what you're trying to do. You want to create a measure that counts the number of tables loaded for a specific day, considering the watermark dates. However, the measure you're using with the COUNTX function and filtering on the watermark column isn't giving you the results you want.

To make it work the way you want, you can tweak your measure like this: 

NrTablesLoaded =
CALCULATE(
COUNTROWS(myTable),
FILTER(
ALL(myTable),
myTable[Watermark] <= MAX(myTable[Watermark]) &&
myTable[DateModified] <= MAX(DateTable[Date])
)
)

[KamEt69 · Accepted solution]

Thank you for the inspiration.

I have modified your code like this:

NrTablesLoaded = 
VAR val =
    CALCULATE (
        COUNTROWS ( MyTable ),
        FILTER (
            ALL ( MyTable ),
            MyTable[WaterMark] + 1
                >= SELECTEDVALUE (
                    D_Date[Date],
                    TODAY ()
                )
        )
    )
RETURN
    IF (
        ISBLANK ( val ),
        0,
        val
    )

Like this it works.