Forum Discussion

deng2431's avatar
deng2431
Icon for Helper I rankHelper I
3 years ago
Solved

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

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!!

 

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

5 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.

  • djurecic's avatar
    djurecic
    Icon for Super User rankSuper User
    3 years ago

    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?

  • appsac1's avatar
    appsac1
    Icon for Helper I rankHelper I
    3 years ago

    Hi djurecic ,

     

    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].

     

  • djurecic's avatar
    djurecic
    Icon for Super User rankSuper User
    3 years ago

    Hi appsac1

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

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