Count rows of a table filtered with multiple conditions and latest date

[Janne_Lappi]

Hello,

my SQL table looks like this:

The oil in the assets in sampled from time to time and the amounts of certain gases formed in the oil are measured.

I want to count the number of assets with the latest CO value (ppm) being more than 1000. This number will be visualized in a report for monitoring.

I managed to display the latest CO value for each asset in a table visual with the following measures:

latest_date =
CALCULATE(LASTNONBLANK('Table1'[sample_date];1))

CO_latest =
IF(HASONEFILTER('Table2'[name]);
LOOKUPVALUE('Table1'[ppm];'Table1'[sample_date];[latest_date];'Table1'[gas];"CO";'Table2'[name];VALUES('Table2'[name]));BLANK())

(Table2 is just a related table where I can find the name for each asset_key.)

So, in addition to that I would need to include the condition "ppm > 1000" and then count the number of rows. I think it is not possible with these two measures. I created the following measure but I don't know how to include the latest date condition.

count_high_CO =
CALCULATE(COUNTROWS(Table1);FILTER(FILTER(Table1;Table1[gas]="CO");Table1[ppm]>1000))

Now this measure counts all the samples with a high CO value in the history, instead of counting only the latest samples with high CO values. Probably this measure is a wrong approach overall.

Thanks for the help in advance.

Best regards,
Janne

[v-eachen-msft]

Hi @Janne_Lappi ,

At first, you need to create a column "rank" to get latest date.

rank =
RANKX (
    FILTER ( 'Table', 'Table'[asset_key] = EARLIER ( 'Table'[asset_key] ) ),
    'Table'[sample_date],
    ,
    DESC,
    DENSE
)

Then create a measure to count the results you want.

count =
COUNTROWS (
    FILTER (
        'Table',
        'Table'[gas] = "CO"
            && 'Table'[rank] = 1
            && 'Table'[ppm] >= 1000
    )
)

[Janne_Lappi]

Hello,

thanks. Your "rank" does not work as a measure: EARLIER/EARLIEST refers to an earlier row context which doesn't exist. As a calculated column it does not seem to work as RANKX is not supported in the Direct Query. Sorry, I forgot to mention in the problem description that I am using DQ.

BR
Janne

[v-eachen-msft]

Hi @Janne_Lappi ,

You could try these two measures:

count =
VAR a =
    CALCULATE (
        MAX ( 'Table'[sample_date] ),
        ALLEXCEPT ( 'Table', 'Table'[asset_key], 'Table'[gas] )
    )
RETURN
    CALCULATE (
        COUNTROWS (
            FILTER (
                'Table',
                'Table'[gas] = "CO"
                    && 'Table'[ppm] >= 1000
                    && a = SELECTEDVALUE ( 'Table'[sample_date] )
            )
        )
    )

and 

finalcount = 
SUMX('Table',[count])

The measure finalcount is your result.

[Janne_Lappi]

Thank you! This works correctly.

Is there any way to modify this to make it work with a sample_date slicer? Then I could go back in time and see what the finalcount value was at a timestamp in the past.

BR,

Janne

[v-eachen-msft · Accepted solution]

Hi @Janne_Lappi ,

You could add date conditions in the filter() formula of "a" like the following DAX:

var a =
CALCULATE(MAX('Table'[sample_date]),ALLEXCEPT('Table','Table'[asset_key],'Table'[gas]),FILTER('Table','Table'[sample_date]>= MINX(ALLSELECTED('Table'[sample_date]),'Table'[sample_date])&&'Table'[sample_date]<= MAXX(ALLSELECTED('Table'[sample_date]),'Table'[sample_date])))

[Anonymous]

Voit tehdä ensin kolumnin johon 3 ehtoa:

- Mitattava kaasu = 'CO'

- PPM yli 1000 

- sample date = max date

palauttaa 1/0 jos kaikki ehdot täyttyy

CO_yli_1000_ppm_jos_max_pvm = IF(Sheet1[PPM] > 1000 && Sheet1[gas] = "CO" && Sheet1[sample_date] = MAX(Sheet1[sample_date]),1,0)

Sitten voit tehdä siitä mittarin johon noiden summa:
Mittari = SUM(Sheet1[CO_yli_1000_ppm_jos_max_pvm])

You can first make a column with 3 if statements to see whether:

- PPM is over 1000 ppm

- Measured gas is 'CO'

- sample date is latest date

and return 1/0 depending on whether all ifs are met

like this: CO_yli_1000_ppm_jos_max_pvm = IF(Sheet1[PPM] > 1000 && Sheet1[gas] = "CO" && Sheet1[sample_date] = MAX(Sheet1[sample_date]),1,0)

Then make a measure to take sum from that column to get number of assets for the latest date that have co ppm over 1000

Mittari = SUM(Sheet1[CO_yli_1000_ppm_jos_max_pvm])

[aswin_k]

Hello Janne, You can get the latest date using "Max" function.
I made a sample dataset based on your data and made this measure, 

Latest Row Count =
VAR LatestDate =
    MAX ( 'Oil Data'[sample_date] )
RETURN
    CALCULATE (
        COUNT ( 'Oil Data'[gas] ),
        'Oil Data'[gas] = "CO",
        'Oil Data'[ppm] > 1000,
        'Oil Data'[sample_date] = LatestDate
    )

Regards
Aswin

[Janne_Lappi]

Aswin, thank you for the reply. This measure doesn't return any value though. I had tried that already, but I don't know why it does not work.

Rasmus, thanks to you as well. The problem in your solution is that MAX(Sheet1[sample_date]) returns just one value which is the latest date in the whole table. Not every asset has its latest oil sample dated at this exact timestamp. The max date should be calculated for each asset individually.

BR,

Janne