DAX - Filtering blank if same key with reference to another column is blank

[deng2431]

Hi DAX team,

I am having filtering concept that's scratching. 

i want to show null or blank if the same [key] is used with reference to the [Payment Date] that is blank, currently it's showing the date '02/02/2022' but if payment date is part of the same key then dont show the date in the [payment date (final)] column. but the requirement also requires showing the max date if same key in [payment date (final)] column does not contain any blanks.

I tried to use the below dax query which does find the max date but i cant filter out if its blank.

Payment date (Final) = calculate(max(Test[Payment Date]),FILTER(allexcept(Test, Test[Key]),Test[Payment Date] <>BLANK()))

Any help is much appreciated. thanks!!

[deng2431]

HI @v-jianboli-msft ,

Thank you!!!! your solution works amazing!!!!

[v-jianboli-msft · Accepted solution]

Hi @deng2431 ,

Please try:

Payment date(Final) = 
VAR _a =
    CALCULATE (
        MAX ( 'Table'[Payment Date] ),
        FILTER ( 'Table', [Key] = EARLIER ( 'Table'[Key] ) )
    )
VAR _b =
    SELECTCOLUMNS (
        FILTER ( 'Table', [Key] = EARLIER ( 'Table'[Key] ) ),
        "Payment Date", [Payment Date]
    )
RETURN
    IF ( BLANK () IN _b, BLANK (), _a )

Final output:

Best Regards,

Jianbo Li

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

[djurecicK2]

Hi @appsac1

 I'm not sure that I totally understand the requirements.

Is this what you are looking for? (calculated column)

[appsac1]

Hi @djurecicK2 ,

No, the opposite. if Payment date is blank and same/recurring [Key] exists with another Payment date then dont show anything in the [Payment date (Final)]. but do show the max date if there are no blank payment date for same/recurring [Key].

[djurecicK2]

@deng2431 So, if Payment date is blank, you want Payment date (Final) to be the Max Payment date with the same Key. Is that right?