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.







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.







      • Juju123's avatar
        Juju123
        Helper III
        2 years ago

        Anonymous 

        Thank you for taking the time to help me.

        it works very well