Forum Discussion

Juju123's avatar
Juju123
Icon for Helper III rankHelper III
2 years ago
Solved

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 gi...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Juju123
Icon for Helper III rankHelper III
2 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Juju123
    Icon for Helper III rankHelper III
    2 years ago

    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] ) ))
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago
      COUNTROWS(FILTER('TABLE', [Difference_test]<>0 && NOT ISBLANK( [Difference_test] ) ))
  • Juju123's avatar
    Juju123
    Icon for Helper III rankHelper III
    2 years ago

    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 )