Filter with two Values

[Anonymous]

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

[Anonymous · Accepted solution]

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

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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 ? 

[Anonymous]

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. 

[Anonymous]

Simulate your data

[Anonymous]

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

[v-easonf-msft]

Hi, @Anonymous 

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

Best Regards,
Community Support Team _ Eason

[Anonymous]

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.