Forum Discussion
CountRow return wrong result
Hi,
I have this table :
I create this measure in dax :
COUNTROWS(FILTER(VALUES('TABLE'[Référence]),[Difference_test]<>0))
The result I should have is 27 but Power BI gives me 3 and that's not good.
How can I find my result of 27 with a DAX formula please?
Thanks 🙂
Hi Juju123 ,
I tested using the pbix file you gave me.
Because your data is so large, I created a slicer using the ‘Reference’ column and the ‘Annee’ column for display:You can use the following DAX to create a measure:
Count Distinct Month = CALCULATE( DISTINCTCOUNT(Feuil6[Mois]), ALLSELECTED(Feuil6), 'Feuil6'[Année] = SELECTEDVALUE(Feuil6[Année]), 'Feuil6'[Référence] = SELECTEDVALUE(Feuil6[Référence]) )And the final output is shown in the following figure, as we mentioned earlier, 44030786AD:F8294-B673 appears in 8 months of 2023, so let's put this to the test:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
17 Replies
Hi Juju123 - when you use the following:
VALUES('TABLE'[Référence])It is creating a table with only 3 rows. There 3 rows contain the distinct values in the Reference column.
You can fix the formula by simply referencing the TABLECOUNTROWS(FILTER('TABLE'),[Difference_test]<>0))Hi Anonymous ,
Thanks you for answer but i try to change dax formula and i have an syntaxe error :
The syntax for “)” is incorrect. (DAX(COUNTROWS(FILTER('TABLE'),[DIFFERENCE_TEST]<>0)))).
COUNTROWS(FILTER('TABLE'),[Difference_test]<>0))Hi Juju123 Sorry I missed a backet after 'Table"). Please remove this.
Thanks but it's not work. The result it's alway 30 and it's not the good result.
I have this table. I want to count the number of ref. when Difference_test <>0. The result it's normaly 27
Hi Juju123 - I am a bit confused, but it look from your first post, there are 24 exceptions. On your second screenshot, the table appears to be cut off so I am not sure how long it is. I think the measure might be including the 6 rows where the result is null or blank(). Consider adding the following:
COUNTROWS(FILTER('TABLE', [Difference_test]<>0 , NOT ISBLANK( [Difference_test] ) ))But on second thoughts, it looks you need to update the measure to include Variables and include an Addcolumns step:
VAR _AddColumn = ADDCOLUMNS( 'Table', "Test", [Difference_Calculation] ) VAR _Filter = FILTER( _AddColumn, [Test] <> 0 ) RETURN COUNTROWS( _Filter )Ok i try this.
But i got another error with DAX formula : Too many arguments were passed to the FILTER function. The maximum number of function arguments is 2.
COUNTROWS(FILTER('TABLE', [Difference_test]<>0 , NOT ISBLANK( [Difference_test] ) ))
Hi Juju123 ,
Because the data in your screenshot is incomplete, I can only create a smaller test data myself, which is shown below:
The test data sheet name is ‘Table’:
You can use the following DAX to create a measure:
Measure = CALCULATE( COUNTROWS('Table'), FILTER( 'Table', 'Table'[different_test] <> 0 && NOT(ISBLANK('Table'[different_test])) ) )And the final output is shown in the following figure:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
I create one drive link where you can download my application and understand my problem.
The One drive link it's :
Can you tell me it's ok for you ?
Hi Juju123 ,
I'm sorry I can see the pbix file you gave me, but I still can't understand what your problem is.
I don't see [different_test] this column of data, this PBIX contains content that doesn't match your question.
