Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MasterSonic
Helper IV
Helper IV

Count if Filter help

Hi guys, the help I need is based on following logic:

I want to find all Test, where value is OK for Task and they have same Code.
As you see In the table
Code is repetitve(result of unpivoting) and Task = Design ,  Code = 1, Test  = Wrong and Value  = OK (FOR 2 ROWS)
Can you help me with any measuremt or column who could catch that kind of scenarios please

CodeTaskTestValue
1DesignWrongOK
2Check if blankInnacurate NO
3Name CorrectOK
4CodeDismissNO
1DesignWrongOK
2Check if blankInnacurate OK
3Name CorrectNO
4CodeDismissOK

 

 

Output should be in table visual shown as below as I have only one distinct value.

TaskCreated Column/Measuremnt  DISTINCT COUNT 
Design                                    1
1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @MasterSonic 

Thanks for reaching out to us.

please try this

sample data

vxiaotang_1-1663746266166.png

isduplicate = 
var _count=COUNTROWS('Table')
return if(_count>1,MIN('Table'[Code]))
Measure = 
var _t= FILTER('Table',[isduplicate]<>BLANK())
return COUNTX(DISTINCT(_t),[Code])

result

vxiaotang_2-1663746281167.png

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
v-xiaotang
Community Support
Community Support

Hi @MasterSonic 

Thanks for reaching out to us.

please try this

sample data

vxiaotang_1-1663746266166.png

isduplicate = 
var _count=COUNTROWS('Table')
return if(_count>1,MIN('Table'[Code]))
Measure = 
var _t= FILTER('Table',[isduplicate]<>BLANK())
return COUNTX(DISTINCT(_t),[Code])

result

vxiaotang_2-1663746281167.png

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

MasterSonic
Helper IV
Helper IV

I try to sort it other way - can you help with this:

I have created measurement:
Distinct Count Attributes = CALCULATE (DISTINCTCOUNT(CleanTable[Task]))

this one five me count of 15
 
I have added SumColumn in power query which summarize columns.

 
I would like to get measurement: (deduction)
Distinct Count Attributes - SumColumn

 

MasterSonic
Helper IV
Helper IV

Nah, that does not work for me. 
And actually very first solution you sent gives me this : <ccon>A circular dependency was detected: Table[Column].</ccon>

I will rephrase my question maybe as I can't  figure out how to solve it.

CodeTaskTestValue
100DesignWrongOK
200Check if blankInnacurate NO
300Name CorrectOK
400CodeDismissNO
100DesignWrongOK
200Check if blankInnacurate OK
300Name CorrectNO
400CodeDismissOK



What I would like to get as visual :

TestCreated Column/Measuremnt  
Innacurate    here will be blank as none of Code,Task,Value are same in rows  (Value column is different)                         
Wronghere is 1 as for all Code rows have value 100 and same Task (Desing) and Value ( OK).

Correcthere will be blank as none of Code,Task,Value are same in rows 
Dismisshere will be blank as none of Code,Task,Value are same in rows 

@MasterSonic 

IF ( COUNTROWS ( DISTINCT ( TableName ) ) <> COUNTROWS ( TableName ), 1 )

<ccon>A circular dependency was detected: Table[Column].</ccon>

Maybe its becouse I have many custom columns here created in PowerQuery

Wolf you please pastea screenshot of the fax code and the visual?

MasterSonic_0-1663244245051.png

 

tamerj1
Super User
Super User

Hi @MasterSonic 

please try

COUNTROWS ( DISTINCT ( TableName ) )

Hi,

sorry I did not mentioned that there are few more columns in this table with veriaty of values

 

I think COUNTROWS ( DISTINCT ( TableName ) ) will not do the job.

Can COUNTROWS ( DISTINCT ( TableName ) )  be specificed just to these 4 columns i mentioned in my 1st post.?

 

@MasterSonic 

yes of course. You select the required columns

COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( TableName, "@Code", [Code], "@Teast", [Test], "@Value", [Value] ) ) )

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.