Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Countifs conatin text

Hi,

I have a one table and it’s contain two columns.

1. If same item has "Not Okay" then return "Not Okay". 2. If same item has "Not Okay" and Orange then return "Not Okay".
2. If same item has Orange then return Orange.
3. If same item has Green then return Green.
4. If same item has Orange or Green in this case return Green

 

I am trying to attempt below DAX code but “The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.”  

 

Answer = IF(CALCULATE(COUNTROWS(Data),Data[Colour Code]="Not Okay"),"Not Okay" || IF(CALCULATE(COUNTROWS(Data),Data[Code],"Orange")=CALCULATE(COUNTROWS(Data),Data[Colour Code],"Green"),Data[Code],"Green"))

I am looking for new calculate column (DAX)

 

In Excel I applying the following formula =IF(COUNTIFS($A$3:$A$101,$A3,$B$3:$B$101,"Not Okay"),"Not Okay",IF(COUNTIFS($A$3:$A$101,$A3,$B$3:$B$101,"Orange")=COUNTIFS($A$3:$A$101,$A3),$B3,"Green")). I would like to apply the same logic in PBI but I am receiving error

 

 

https://www.dropbox.com/s/q4rn361y01r40x2/Countifs%20contain%20text-26-09-21.pbix?dl=0

 

Data:

ItemCodeDesired Result
123OrangeOrange
124GreenGreen
125OrangeOrange
126GreenGreen
127OrangeOrange
128OrangeOrange
135OrangeGreen
135GreenGreen
136Not OkayNot Okay
136Not OkayNot Okay
137OrangeOrange
137OrangeOrange
137OrangeOrange
138GreenGreen
138OrangeGreen
138OrangeGreen
139OrangeOrange
139OrangeOrange
140GreenGreen
140OrangeGreen
141OrangeOrange
141OrangeOrange
141OrangeOrange
142GreenGreen
142OrangeGreen
142OrangeGreen
143GreenGreen
143GreenGreen
143OrangeGreen
144Not OkayNot Okay
144Not OkayNot Okay
144OrangeNot Okay
145Not OkayNot Okay
145Not OkayNot Okay
145Not OkayNot Okay
145Not OkayNot Okay
147GreenGreen
147GreenGreen
148OrangeOrange
148OrangeOrange
149GreenGreen
149GreenGreen
150OrangeOrange
150OrangeOrange
151GreenGreen
151GreenGreen
152OrangeOrange
152OrangeOrange
152OrangeOrange
153Not OkayNot Okay
153Not OkayNot Okay
153OrangeNot Okay
1999Not OkayNot Okay
1999Not OkayNot Okay
1999OrangeNot Okay
155GreenNot Okay
155Not OkayNot Okay
156OrangeGreen
156GreenGreen
157OrangeOrange
157OrangeOrange
154Not OkayNot Okay
154OrangeNot Okay
154Not OkayNot Okay
155Not OkayNot Okay
155GreenNot Okay
155OrangeNot Okay
156OrangeGreen
156GreenGreen
156OrangeGreen
156OrangeGreen
198Not OkayNot Okay
198Not OkayNot Okay
198Not OkayNot Okay
198Not OkayNot Okay
198Not OkayNot Okay
200GreenGreen
200GreenGreen
200GreenGreen
200GreenGreen
200GreenGreen
200GreenGreen
198Not OkayNot Okay
198Not OkayNot Okay
198Not OkayNot Okay
198Not OkayNot Okay
198Not OkayNot Okay
21111Not OkayNot Okay
21111Not OkayNot Okay
21111Not OkayNot Okay
21111Not OkayNot Okay
21111Not OkayNot Okay
21111Not OkayNot Okay
21111Not OkayNot Okay
21111Not OkayNot Okay
21111Not OkayNot Okay
21111Not OkayNot Okay
21111Not OkayNot Okay
21111OrangeNot Okay

 

Need Help
  • parry2k's avatar
    parry2k
    4 years ago

    Saxon10 tweak my DAX expression as below:

     

    Answer1 = 
VAR __code = Report[Item]
VAR __table = CALCULATETABLE ( Data,  Data[Item] = __code )
VAR __noOk = COUNTX ( __table, IF ( [Code] = "Not Okay", 1 ) )
VAR __orange = COUNTX ( __table, IF ( [Code] = "Orange", 1 ) )
VAR __green = COUNTX ( __table, IF ( [Code] = "Green", 1 ) )

RETURN
SWITCH ( TRUE(),
    NOT ISBLANK ( __noOk ), "Not Okay",
    NOT ISBLANK ( __orange ) && NOT ISBLANK ( __green ), "Green", 
    NOT ISBLANK ( __orange ), "Orange",
    NOT ISBLANK ( __green ), "Green",
    "Unknown"
)

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    Write this calculated column formula in the Report worksheet

    Answer = if(CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[ITEM]=EARLIER(REPORT[ITEM])))=0,"NA",if(CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[ITEM]=EARLIER(REPORT[ITEM])&&DATA[CODE]="Not Okay"))>=1,"Not Okay",IF(CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[ITEM]=EARLIER(REPORT[ITEM])))=CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[ITEM]=EARLIER(REPORT[ITEM])&&DATA[CODE]="Orange")),"Orange","Green")))

    Hope this helps.

8 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    Saxon10 add new column using following expression:

     

    Answer = 
VAR __code = Data[Item]
VAR __table = CALCULATETABLE ( Data, ALL ( Data ), Data[Item] = __code )
VAR __noOk = COUNTX ( __table, IF ( [Code] = "Not Okay", 1 ) )
VAR __orange = COUNTX ( __table, IF ( [Code] = "Orange", 1 ) )
VAR __green = COUNTX ( __table, IF ( [Code] = "Green", 1 ) )

RETURN
SWITCH ( TRUE(),
    NOT ISBLANK ( __noOk ), "Not Okay",
    NOT ISBLANK ( __orange ) && NOT ISBLANK ( __green ), "Green", 
    NOT ISBLANK ( __orange ), "Orange",
    NOT ISBLANK ( __green ), "Green",
    "Unknown"
)

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    This calculated column formula works

    =if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Item]=EARLIER(Data[Item])&&Data[Code]="Not Okay"))>=1,"Not Okay",IF(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Item]=EARLIER(Data[Item])))=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Item]=EARLIER(Data[Item])&&Data[Code]="Orange")),"Orange","Green"))

    Hope this helps.

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      Ashish_Mathur  and parry2k ,

       

      Thanks for your solution. The both solution is working well.

       

      I need one more advise please. I would like to get the same result with in-between two tables.

      I have a two tables data and Report. The both tables item columns has duplicated.

      I would like to pull the colour code from data table into report table according to the item.

      1. If same item has "Not Okay" then return "Not Okay". 2. If same item has "Not Okay" and Orange then return "Not Okay".
      2. If same item has Orange then return Orange.
      3. If same item has Green then return Green.
      4. If same item has Orange or Green in this case return Green

      5. If Item can't found in data table then return "NA".

       

      Some of the item 2 times repeated in data table but in report table maybe it's unique. Example -135.

      PBI file attached for your reference.

       

      https://www.dropbox.com/s/fjc544dmnk6jiie/Countifs%20contain%20text-1-26-09-21.pbix?dl=0

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Write this calculated column formula in the Report worksheet

        Answer = if(CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[ITEM]=EARLIER(REPORT[ITEM])))=0,"NA",if(CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[ITEM]=EARLIER(REPORT[ITEM])&&DATA[CODE]="Not Okay"))>=1,"Not Okay",IF(CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[ITEM]=EARLIER(REPORT[ITEM])))=CALCULATE(COUNTROWS(DATA),FILTER(DATA,DATA[ITEM]=EARLIER(REPORT[ITEM])&&DATA[CODE]="Orange")),"Orange","Green")))

        Hope this helps.

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    Saxon10 tweak my DAX expression as below:

     

    Answer1 = 
VAR __code = Report[Item]
VAR __table = CALCULATETABLE ( Data,  Data[Item] = __code )
VAR __noOk = COUNTX ( __table, IF ( [Code] = "Not Okay", 1 ) )
VAR __orange = COUNTX ( __table, IF ( [Code] = "Orange", 1 ) )
VAR __green = COUNTX ( __table, IF ( [Code] = "Green", 1 ) )

RETURN
SWITCH ( TRUE(),
    NOT ISBLANK ( __noOk ), "Not Okay",
    NOT ISBLANK ( __orange ) && NOT ISBLANK ( __green ), "Green", 
    NOT ISBLANK ( __orange ), "Orange",
    NOT ISBLANK ( __green ), "Green",
    "Unknown"
)

     

    ✨ Follow us on LinkedIn

     

    Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

    • Saxon10's avatar
      Saxon10
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      parry2k 

       

      Thanks for your help and with VAR solution it's new thing to learn PBI.