topic Re: Filter with two Values in DAX Commands and Tips

Filter with two Values

Anonymous — Wed, 14 Sep 2022 14:56:33 GMT

Hello,

I have a problem in DAX and can not find a solution after days of searching. Perhaps someone can give me a hint?
I have the current MEASURE Definition:

CostUSER = VAR currentuser = [SELECTUSER] -- comes from other measure VAR varD = SUMMARIZE ( FILTER(USERDepartment,USERDepartment[USERID]=currentuser), USERDepartment[Department] ) ) VAR calcCost = sumx(FILTER(RegionCost,related(DepartmentRegion[Department]) IN varD),RegionCost[Cost]) RETURN IF(ISBLANK(varD),BLANK(),calcCost)

How it possible to FILTER related(DepartmentRegion[Department]) with two Values ?
The user in USERDepartment can be assigned two 2 Departments. If a choose with a slicer one department everything works but with two Departments it says that one value instead of two was expected.
I try different things like TREATES, but with no success.

The Tables

One hint we be so great! Thank you! 🙂

Re: Filter with two Values

v-easonf-msft — Thu, 15 Sep 2022 08:19:51 GMT

Hi, Anonymous

Have you tried changing the one-to-many single-directional relationships to bidirectional relationships?

Best Regards,
Community Support Team _ Eason

Re: Filter with two Values

Anonymous — Tue, 20 Sep 2022 08:03:06 GMT

I am not sure, how this can help me in this case. I want to use the measure and do not know how to modify it.

Re: Filter with two Values

Anonymous — Tue, 20 Sep 2022 09:24:09 GMT

ISBLANK(<value>) Parameters should be value , can't be a table. You can change this function to COUNTROWS.

IF(COUNTROWS(varD)=0,BLANK(),calccost)

Re: Filter with two Values

Anonymous — Tue, 20 Sep 2022 12:03:02 GMT

In this case how I can filter if I have not 1 value but 2 values in the column?

Re: Filter with two Values

Anonymous — Tue, 20 Sep 2022 12:08:55 GMT

Actually your DAX formular can filter multi values except isblank function is wrong. You can change it and test.

Re: Filter with two Values

Anonymous — Tue, 20 Sep 2022 15:04:37 GMT

But if i choose User 1 I will have two values in varD and get a error.
VAR calcCost = sumx(FILTER(RegionCost,related(DepartmentRegion[Department]) IN varD),RegionCost[Cost])
How i can FIlter if varD has two values ?

Re: Filter with two Values

Anonymous — Wed, 21 Sep 2022 00:48:26 GMT

It is so wired. But for me it works. VarD is a table with multi values,if you select Userid 1. Calccost DAX function is correct.

Re: Filter with two Values

Anonymous — Wed, 21 Sep 2022 01:22:20 GMT

Simulate your data

Re: Filter with two Values

Anonymous — Wed, 21 Sep 2022 05:17:52 GMT

Thank you, Thank you!

VAR calcCost = sumx(FILTER(RegionCost,related(DepartmentRegion[Department]) IN varD),RegionCost[Cost])

RETURNIF(COUNTROWS(varD)=0,BLANK(),calcCost)

I do not realize, that ISBLANK is the error. Now it works with countrows! 🙂