if column in a table has value x and a different column in the same table has value y return z

[ChrisMcHugh]

I have tried many different ways to get this to work, including firstnonblanks, maxes etc but nothing seems to be working for me. Firstnonblank does return a value, but it only returns the first value whereas I need it to filter through the table to find a value further down which corresponds to the filters placed.

This is the current code I am working with: 

IF (FIRSTNONBLANK(VW_STAGING_TO_ACQ_LOAD_DETAIL[LOAD_STATUS],1) = "LOAD_FAILED",

0,

IF (FIRSTNONBLANK(VW_STAGING_TO_ACQ_LOAD_DETAIL[LOAD_STATUS],1) = "PARTIALLY_LOADED",

1,

2))

[PaulDBrown]

Please provide sample data or PBIX file and a depiction of the expected outcome.

[ChrisMcHugh]

IF(STAGING_ACQ[LOAD_STATUS] = FAILED_LOAD AND STAGING_ACQ[TABLE_NAME] = ROUTES, 0

This 0 will then be input into a button colour so that if measure = 0, make the button red

[PaulDBrown]

If you are selecting values from slicers, try:

IF(AND(SELECTEDVALUE(STAGING_ACQ[LOAD_STATUS]) = "FAILED_LOAD", SELECTEDVALUE(STAGING_ACQ[TABLE_NAME]) = "ROUTES", 0)

[ChrisMcHugh]

Hi @PaulDBrown I am not pulling from slicers unfortunately but from actual datasets, the dataset gets updated every few minutes and each time the process runs, it loads files into snowflake and updates the table accordingly on the load status. So I want to use that if routes has an issue (load failed) then to set it to 0. Then in formatting the colour button by rules, I will set it that if it = 0 then red

[PaulDBrown]

Sorry, I'm not understanding the issue. The dataset gets loaded and then you need to check if routes has an issue. But you need a context to check (a row/rows in a table, a selection...9 So what is the measure supposed to compute?

[ChrisMcHugh]

@PaulDBrown 

ROUTES FAILED JOBS =

VAR LOAD_FAILED = CALCULATE(COUNT(VW_STAGING_TO_ACQ_LOAD_DETAIL[LOAD_STATUS]), FILTER(VW_STAGING_TO_ACQ_LOAD_DETAIL, VW_STAGING_TO_ACQ_LOAD_DETAIL[LOAD_STATUS] = "LOAD_FAILED"),VW_STAGING_TO_ACQ_LOAD_DETAIL[TABLE_NAME] = "ROUTES")
VAR IF_LOAD_FAILED = IF(ISBLANK(LOAD_FAILED), 0, 1)

VAR PARTIALLY_LOADED = CALCULATE(COUNT(VW_STAGING_TO_ACQ_LOAD_DETAIL[LOAD_STATUS]), FILTER(VW_STAGING_TO_ACQ_LOAD_DETAIL, VW_STAGING_TO_ACQ_LOAD_DETAIL[LOAD_STATUS] = "PARTIALLY_LOADED"),VW_STAGING_TO_ACQ_LOAD_DETAIL[TABLE_NAME] = "ROUTES")
VAR IF_LOADED = IF(ISBLANK(PARTIALLY_LOADED), 0, 2)

VAR LOADED = CALCULATE(COUNT(VW_STAGING_TO_ACQ_LOAD_DETAIL[LOAD_STATUS]), FILTER(VW_STAGING_TO_ACQ_LOAD_DETAIL, VW_STAGING_TO_ACQ_LOAD_DETAIL[LOAD_STATUS] = "LOADED"),VW_STAGING_TO_ACQ_LOAD_DETAIL[TABLE_NAME] = "ROUTES")
VAR IF_LOADED = IF(ISBLANK(LOADED), 0, 3)

I am trying this syntax at the moment as a different approach but the syntax has an issue of some kind

[ChrisMcHugh]

I want it to check each row where TABLE_NAME is ROUTES and if in any of those rows, LOAD_STATUS = LOAD_FAILED then to put the measure value to 0

[PaulDBrown · Accepted solution]

In that case try:

Flag =
IF (
    COUNTROWS (
        FILTER (
            STAGING_ACQ,
            STAGING_ACQ[LOAD_STATUS] = "FAILED_LOAD"
                && STAGING_ACQ[TABLE_NAME] = "ROUTES"
        )
    ) > 0,
    0
)