topic Re: DAX - Filtering blank if same key with reference to another column is blank in DAX Commands and Tips

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

deng2431 — Wed, 09 Nov 2022 18:47:24 GMT

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

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

djurecic — Wed, 09 Nov 2022 22:28:18 GMT

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?

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

appsac1 — Wed, 09 Nov 2022 22:48:14 GMT

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

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

djurecic — Wed, 09 Nov 2022 22:54:55 GMT

Hi appsac1

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

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

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

v-jianboli-msft — Thu, 10 Nov 2022 07:05:42 GMT

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.

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

deng2431 — Thu, 10 Nov 2022 23:11:58 GMT

HI v-jianboli-msft ,

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