Forum Discussion

cheid_4838's avatar
cheid_4838
Helper IV
1 year ago
Solved

Countdistinct is not Summing correctly

I've created the below logic (Total Cal Days) to count distinct the number of calendar days and then put the sum of those values as the total.  I am exepecting the total to be 32, not 2 (see screenshot).  Then I subtract "Total Cal Days" from "Tractor Run Days" to get 11 days "Not Running".  I use the same logic to calculate "Tractor Run Days" and it works fine, but it does not work for Total Cal Days.  Anything I maybe missing?  Thanks for your help

 

 

Total Cal Days =
VAR _COUNT =
    SUMMARIZE(
        'CalendarLookup',
        "StartDateDistinct",COUNT(CalendarLookup[Date]))

    RETURN
    SUMX(_COUNT,[StartDateDistinct])
 
 
Tractor Run Days =
VAR _COUNT =
    SUMMARIZE(
        'Tractor Utilization',
        "StartDateDistinct",COUNT('Tractor Utilization'[StartDate]))

    RETURN
    SUMX(_COUNT,[StartDateDistinct])
 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi cheid_4838  - You're summarizing the entire CalendarLookup table without any grouping columns. This means it returns a single row, and the count becomes the total number of dates in the current filter context.

    try below calculation :

    Total Cal Days =
    SUMX(
    VALUES('YourTable'[GroupColumn]), -- e.g., TractorID
    CALCULATE(DISTINCTCOUNT('CalendarLookup'[Date]))
    )

     

    Hope this works. please check and let know.

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi cheid_4838  - You're summarizing the entire CalendarLookup table without any grouping columns. This means it returns a single row, and the count becomes the total number of dates in the current filter context.

    try below calculation :

    Total Cal Days =
    SUMX(
    VALUES('YourTable'[GroupColumn]), -- e.g., TractorID
    CALCULATE(DISTINCTCOUNT('CalendarLookup'[Date]))
    )

     

    Hope this works. please check and let know.

  • v-menakakota's avatar
    v-menakakota
    Community Support
    1 year ago

    Hi cheid_4838 ,
    Thanks for reaching out to the Microsoft fabric community forum.

    I would also take a moment to thank  rajendraongole1 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. 

    Thank you,
    Menaka.

    • v-menakakota's avatar
      v-menakakota
      Community Support
      1 year ago

      Hi  cheid_4838  ,
      I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution so that other community members can find it easily. 

      Best Regards, 
      Menaka
      Community Support Team

      • v-menakakota's avatar
        v-menakakota
        Community Support
        1 year ago

        Hi cheid_4838   ,

        May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster. 

        Best Regards, 
        Menaka.
        Community Support Team