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 ,
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 ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

      Anonymous

      Yes that's what I want! 🙂

      Ok i wait , thanks you so much

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.