Compare between different tables

[Apsawhney]

Hi,

I have a report whose dataset needs to be refreshed every hour. Due to 8x limit in import I had to use DIrect Query mode. 

So here is my problem. I have 2 tables - Assessments and thresholds.

Assessment table contains the latest assessment count for various partners. Threshold table contains the threshold for assessment count for each partner. 

I created a measure to compare both and if the count is less than threshold then give 0/1.

Measure = IF(MAXX(Assessment, Assessmentcount)<MAXX(Threshold, Thresholdcount),1,0)

What I want is a measure which gives value as "Healthy" if all the values as per above measure are 0 and the value as "Unhealthy" if any one of the value from above measure is 1. 

Note that the assessment count is dynamic and it will refresh every hour and we only compare the latest count with threshold count. 

I tried to change the above measure to a calculated column but due to direct query mode I have limitations. 

Please note that I have posted similar kind of problem beofre and had received some solutions which worked but now I had to change the strcuture of the tables and hence I am stuck again. 

[v-jayw-msft]

Hi @Apsawhney,

Please check following measures and see if the result achieve your expectation:

Measure =

IF (

    CALCULATE (

        MAX ( Assessment[Assessmentcount] ),

        FILTER ( Assessment, Assessment[CAT] = MAX ( Assessment[CAT] ) )

    )

        < MAX ( Threshold[Thresholdcount] ),

    1,

    0

)

Measure 2 = IF(SUMX(Threshold,[Measure])>0,"Unhealthy","Healthy")

Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Apsawhney]

HI,

I had to use the MAXX function in the filter instead of MAX. Seems to working for now. Will test it for a few days to make sure it picks up all the various threshold breaches.