Forum Discussion

Tinus1905's avatar
Tinus1905
Resolver I
2 years ago
Solved

PowerBi filter multiple columns with multiple filters

Hi,

 

Im struggling a little bit with an measure. 

I have the following table. 

ID       Status           Type          Name

123acceptyesyellow
123acceptyes 
456not possiblestandardgreen
456not possiblestandard 
789not possiblestandardgreen
135acceptno 

 

I want to distinctcount the ID column, and then calculate Type column = "Yes" and "No" AND the Status column = "not possible" and the Name column = "Green". 

So the outcome must be: 4

 

ID       Status            Type         Name

123acceptyesyellow
456not possiblestandardgreen
789not possiblestandardgreen
135acceptno 

 

My formula is: 

 

Count =
    CALCULATE(
    DISTINCTCOUNT(tableA[ID]),
    tableA[Type] IN {"yes", "no"},
    tableA[Status] = "not possible" &&
    tableA[Name] = "green")
 
The outcome here is 0, but that is wrong. 
 
How can I solve this. 
Need Help
  • Tinus1905's avatar
    Tinus1905
    2 years ago

    This formula must be it.

     

    Count =
    CALCULATE(
    DISTINCTCOUNT(tableA[ID]),

    (tableA[Type] = "yes" || tableA[Type] = "no") ||
    (tableA[Status] = "not possible" &&
    tableA[Name] = "green"))

     

    The only question I have now is, ID 456 is double and with distinctcount I get 1 count. But will it count the row with column "name" and "green" value or the other one. So is distinccount after the filters has been set or is it before the filters. 

  • Uzi2019's avatar
    Uzi2019
    2 years ago

    Hi Tinus1905 
    try below measure.

     

    Count of id =
    CALCULATE(DISTINCTCOUNT('Table'[ID]),AND('Table'[Status]="not possible" , 'Table'[Name]="green") || 'Table'[Type]="yes"|| 'Table'[Type]= "no")
     

     

     

    I hope I answered your question!

6 Replies

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    2 years ago

    Hi Tinus1905 
    try below measure.

     

    Count of id =
    CALCULATE(DISTINCTCOUNT('Table'[ID]),AND('Table'[Status]="not possible" , 'Table'[Name]="green") || 'Table'[Type]="yes"|| 'Table'[Type]= "no")
     

     

     

    I hope I answered your question!

  • Alican_C's avatar
    Alican_C
    Resolver II
    2 years ago

    Give it a shot with this;

    Count =
    CALCULATE(
    DISTINCTCOUNT(tableA[ID]),
    FILTER(
    tableA,
    (tableA[Type] = "yes" || tableA[Type] = "no") &&
    tableA[Status] = "not possible" &&
    tableA[Name] = "green"
    )
    )

  • Alican_C's avatar
    Alican_C
    Resolver II
    2 years ago

    just realized that there isnt actually any ID matching with all conditions you looked for. 

    I highlighted values matching the condition with green.  See below there is no single line meets your all
    Type column = "Yes" and "No" AND the Status column = "not possible" and the Name column = "Green". .

    ID       Status           Type          Name

    123acceptyesyellow
    123acceptyes 
    456not possiblestandardgreen
    456not possiblestandard 
    789not possiblestandardgreen
    135acceptno 

     

     

    • Uzi2019's avatar
      Uzi2019
      Community Champion
      2 years ago

      Hi Tinus1905 

      try below measure

      Count of id =
      CALCULATE(DISTINCTCOUNT('Table'[ID]),AND('Table'[Status]="not possible" , 'Table'[Name]="green") || 'Table'[Type]="yes"|| 'Table'[Type]= "no")

       

       

      I hope  I naswered your question!

       

       

  • Tinus1905's avatar
    Tinus1905
    Resolver I
    2 years ago

    This formula must be it.

     

    Count =
    CALCULATE(
    DISTINCTCOUNT(tableA[ID]),

    (tableA[Type] = "yes" || tableA[Type] = "no") ||
    (tableA[Status] = "not possible" &&
    tableA[Name] = "green"))

     

    The only question I have now is, ID 456 is double and with distinctcount I get 1 count. But will it count the row with column "name" and "green" value or the other one. So is distinccount after the filters has been set or is it before the filters.