Forum Discussion

Juju123's avatar
Juju123
Helper 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.







Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

    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's avatar
      Anonymous
      Not applicable
      2 years ago

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

        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 ?