Forum Discussion
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
| Code | Task | Test | Value |
| 1 | Design | Wrong | OK |
| 2 | Check if blank | Innacurate | NO |
| 3 | Name | Correct | OK |
| 4 | Code | Dismiss | NO |
| 1 | Design | Wrong | OK |
| 2 | Check if blank | Innacurate | OK |
| 3 | Name | Correct | NO |
| 4 | Code | Dismiss | OK |
Output should be in table visual shown as below as I have only one distinct value.
| Task | Created Column/Measuremnt DISTINCT COUNT |
| Design | 1 |
Hi MasterSonic
Thanks for reaching out to us.
please try this
sample data
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
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.
10 Replies
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.?yes of course. You select the required columns
COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( TableName, "@Code", [Code], "@Teast", [Test], "@Value", [Value] ) ) )
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.Code Task Test Value 100 Design Wrong OK 200 Check if blank Innacurate NO 300 Name Correct OK 400 Code Dismiss NO 100 Design Wrong OK 200 Check if blank Innacurate OK 300 Name Correct NO 400 Code Dismiss OK What I would like to get as visual :
Test Created Column/Measuremnt Innacurate here will be blank as none of Code,Task,Value are same in rows (Value column is different) Wrong here is 1 as for all Code rows have value 100 and same Task (Desing) and Value ( OK). Correct here will be blank as none of Code,Task,Value are same in rows Dismiss here will be blank as none of Code,Task,Value are same in rows 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
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 15I have added SumColumn in power query which summarize columns.I would like to get measurement: (deduction)
Distinct Count Attributes - SumColumnHi MasterSonic
Thanks for reaching out to us.
please try this
sample data
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
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.
