Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Filter with two Values

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's avatar
    Anonymous
    3 years ago

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

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

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      Anonymous
      Not applicable

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        • Actually your DAX formular can filter multi values except isblank function is wrong. You can change it and test.
  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support

    Hi, Anonymous 

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

     

    Best Regards,
    Community Support Team _ Eason

    • Anonymous's avatar
      Anonymous
      Not applicable

      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.