Forum Discussion
Verify data from different columns to verify data from another
I need to count the TAG (Team TAG) that are correct, for that I have to count the blanks in the "Name Rule" column, the values in the "Team Number" column that contain letters and count from the "Repeated" column the values other than 1.
Please help you
Hello,Ashish_Mathur ,Daniel29195 ,thanks for your concern about this issue.
Your answer is excellent! And I would like to share some additional solutions below.
Hi,Loreto-F .I am glad to help you.
You can refer to my test below
Here is my test data:Since the filtering conditions you have given are not very specific, I have created a variety of scenarios to simulate the possible filtering conditions, and the results of the filtering of non-repeating counting
1. If you want to filter out the data range as follows, you can try the following measure2:
Measure 2 = VAR _table = SUMMARIZE('Table','Table'[Team TAG],'Table'[Team Number],'Table'[Repeated],'Table'[Name Rule],"ISZIMU",IFERROR(INT('Table'[Team Number]),0)) RETURN COUNTROWS(SUMMARIZE(FILTER(_table,'Table'[Repeated]<>1),'Table'[Team TAG]))2. If you want to filter out the results as follows, you can try the following measure.
Measure = VAR _table = SUMMARIZE('Table','Table'[Team TAG],'Table'[Team Number],'Table'[Repeated],'Table'[Name Rule],"ISZIMU",IFERROR(INT('Table'[Team Number]),0)) RETURN COUNTROWS(SUMMARIZE(FILTER(_table,'Table'[Repeated]<>1&&([ISZIMU]=0||'Table'[Name Rule]=BLANK())),'Table'[Team TAG]))3. If you only want to filter out records with 'Table' [Repeated] column <> 1, you can use measure3 below:
Measure 3 = VAR _table = SUMMARIZE('Table','Table'[Team TAG],'Table'[Team Number],'Table'[Repeated],'Table'[Name Rule],"ISZIMU",IFERROR(INT('Table'[Team Number]),0)) RETURN COUNTROWS(SUMMARIZE(FILTER(_table,'Table'[Repeated]=1&&('Table'[Team Number]<>BLANK()||[ISZIMU]<>0)),'Table'[Team TAG]))If you can explain your filtering criteria in detail and explain the filtering relationship in it and also please share out the pbix file which does not contain sensitive data, it will help in solving your problem.
Note that the SUMMARIZE() function itself has a de-emphasis role, so there is no need to use distinctcount() again
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
assuming the table name is : tbl
create the following measure :
_measure =
var ds =
filter (tbl ,
tbl[name rule] = blank() && tbl[repeated] <> 1 && isnumber( tbl[team number] )
)return
countrows( ds)
let me know if this helps.
Hello, thank you for your help. I adapted it until Power BI validated it, but it throws "(blank)" at me. the same with the previous solution.
Hi,
Drag this measure to a card visual
=calculate(countrows(Data),data[name rule]=blank()&&[repeated]<>1)
Hope this helps.
hello thanks for your help, but it didn't work either, the error is in the syntax =blank()
I tried changing blank to blank, but it didn't work either
Hi,
I have assumed that "name rule" and "repeated" are columns in the Data table.
Thank you for your help, but the measurement has errors that I don't know, it didn't work.
Hello,Ashish_Mathur ,Daniel29195 ,thanks for your concern about this issue.
Your answer is excellent! And I would like to share some additional solutions below.
Hi,Loreto-F .I am glad to help you.
You can refer to my test below
Here is my test data:Since the filtering conditions you have given are not very specific, I have created a variety of scenarios to simulate the possible filtering conditions, and the results of the filtering of non-repeating counting
1. If you want to filter out the data range as follows, you can try the following measure2:
Measure 2 = VAR _table = SUMMARIZE('Table','Table'[Team TAG],'Table'[Team Number],'Table'[Repeated],'Table'[Name Rule],"ISZIMU",IFERROR(INT('Table'[Team Number]),0)) RETURN COUNTROWS(SUMMARIZE(FILTER(_table,'Table'[Repeated]<>1),'Table'[Team TAG]))2. If you want to filter out the results as follows, you can try the following measure.
Measure = VAR _table = SUMMARIZE('Table','Table'[Team TAG],'Table'[Team Number],'Table'[Repeated],'Table'[Name Rule],"ISZIMU",IFERROR(INT('Table'[Team Number]),0)) RETURN COUNTROWS(SUMMARIZE(FILTER(_table,'Table'[Repeated]<>1&&([ISZIMU]=0||'Table'[Name Rule]=BLANK())),'Table'[Team TAG]))3. If you only want to filter out records with 'Table' [Repeated] column <> 1, you can use measure3 below:
Measure 3 = VAR _table = SUMMARIZE('Table','Table'[Team TAG],'Table'[Team Number],'Table'[Repeated],'Table'[Name Rule],"ISZIMU",IFERROR(INT('Table'[Team Number]),0)) RETURN COUNTROWS(SUMMARIZE(FILTER(_table,'Table'[Repeated]=1&&('Table'[Team Number]<>BLANK()||[ISZIMU]<>0)),'Table'[Team TAG]))If you can explain your filtering criteria in detail and explain the filtering relationship in it and also please share out the pbix file which does not contain sensitive data, it will help in solving your problem.
Note that the SUMMARIZE() function itself has a de-emphasis role, so there is no need to use distinctcount() again
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
