count filtered values based on several criteria (from another table)

[AFra]

Hi pbi community, 

I am struggeling with a problem : I need to count the LiaisonKey on the table 'Demande'. 

In this main table, all the liaisonkeys are distinctvalues. 

In the rad table, some liaisonkeys appear more than once. 

I need to count all the liaisonkey in Demande excluding all the liaisonkey in Rad IF : 

- earliest DateRadiation for each liaisonkey is > 01-05-2021 AND

- DtDerad for that same liaisonkey and Dateradiation is blank

I tried a simple measure : 

test =

var my_date = date(01,05,2021)

return

calculate(DISTINCTCOUNT(Demande[LIAISONKEY]),

LASTDATE(Radiatio[DateRadiation])<my_date,

ISBLANK(Radiatio[DtDerad])

)

but it doesn't work. I tried a calculated column in Demande table with lookupvalues, but didn't work neither.

Any ideas? 

 here's a sample of my dataset : https://www.transfernow.net/dl/20211014cnFR4oG1/dJ6BZELN

[v-janeyg-msft · Accepted solution]

Hi, @AFra 

According to your description, You can create a measure.

Like this:

Measure2 =
VAR eardate =
    CALCULATE (
        MIN ( Rad[DateRadiation] ),
        FILTER ( ALL ( Rad ), [LiaisonKey] = SELECTEDVALUE ( Rad[LiaisonKey] ) )
    )
RETURN
    CALCULATE (
        DISTINCT ( Demande[LIAISONKEY] ),
        FILTER ( ALL ( Rad ), [DtDerad] <> BLANK () && eardate <= DATE ( 2021, 1, 5 ) )
    )

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey