CountRow return wrong result

[Juju123]

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 🙂

[Anonymous]

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.

[Juju123]

Hi @Anonymous ,

I create one drive link where you can download my application and understand my problem.

The One drive link it's :  

test_distinct_value.pbix

Can you tell me it's ok for you ?

[Anonymous]

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.

[Juju123]

@Anonymous 

I modified my example to make it easier to understand.

Do you have another platform that you use that I can send you my pbix to because without the pbix it will be difficult to understand.

what I want to see appear in the "Nb reference" column is the number 1 because we only have one reference.

however in my Power Bi table, for a reference, I have several lines, because a reference has several dates, several stores etc. But what I want is only to count the reference number without the other columns of the table which duplicates the rows for me.

example: For screenshot reference, if we take the month of October 2023, I should have 1 and not 5.

Is that clear ?

[Anonymous]

Hi @Juju123 ,
Do you mean so? (This is what I created myself and I think you want to get, not what DAX gets):

If that's what you mean, then your question should be understood as, how many months of the year do different references appear? For example, as you can see in the screenshot, the reference in the screenshot appears in a total of 8 months in 2023, so 8 is the result you want, right?
If this is your problem, please be patient as I am close to the end of work today and I will try to solve this problem for you when I come to work tomorrow.

Best Regards

[Juju123]

@Anonymous

Yes that's what I want! 🙂

Ok i wait , thanks you so much

[Anonymous · Accepted solution]

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.

[Juju123]

@Anonymous 

Thank you for taking the time to help me.

it works very well

[Juju123]

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

[Daryl-Lynch-Bzy]

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 ) 

[Juju123]

Hi 

i put the example of my data in this pbix 

you can download it and see my problem with countrow 

https://fromsmash.com/Distinct-value-selected-pbi

[Juju123]

For the second DAX formula, i have this error : 

The syntax for “VAR” is incorrect. (DAX(ADDCOLUMNS('TABLE',"Test", [Difference_Test])VAR _Filter FILTER(_AddColumn,[Test] <> 0)RETURN COUNTROWS( _Filter ) )).

VAR _AddColumn = ADDCOLUMNS('TABLE',"Test", [DIFFERENCE_TEST])
VAR _Filter = FILTER(_AddColumn,[Test] <> 0)
RETURN
   COUNTROWS( _Filter ) 

[Juju123]

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] ) ))

[Daryl-Lynch-Bzy]

COUNTROWS(FILTER('TABLE', [Difference_test]<>0 && NOT ISBLANK( [Difference_test] ) ))

[Juju123]

Hi @Daryl-Lynch-Bzy ,

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))

[Daryl-Lynch-Bzy]

Hi @Juju123 Sorry I missed a backet after 'Table").  Please remove this.

[Daryl-Lynch-Bzy]

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 TABLE

COUNTROWS(FILTER('TABLE'),[Difference_test]<>0))