Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
KamEt69
Frequent Visitor

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

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?

2 ACCEPTED SOLUTIONS
Muhammad110
Helper I
Helper I

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])
)
)

View solution in original post

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.

View solution in original post

2 REPLIES 2
Muhammad110
Helper I
Helper I

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])
)
)

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.