Forum Discussion

doubleclick's avatar
doubleclick
Icon for Resolver I rankResolver I
4 years ago
Solved

Change from Countx to DistinctCount

Hi All,

 

I have a cool formula here but i'm having trouble converting it to a distinct count 

I want to change the below into a distinctcount of a column called [Worker] - how can i change this?

 

Tenure by Day (Count over 365 days) = 
VAR SoM=min('Calendar'[Date])
VAR EoM=max('Calendar'[Date])
RETURN countx(CALCULATETABLE(MainTable,All('Calendar'),filter(MainTable,(DATEDIFF(MainTable[Start Date],EoM,DAY)>=365)),MainTable[Start Date]<=EoM,ABS(
MainTable[End Date]>=SoM)),DATEDIFF(MainTableDE[Start Date],EoM,DAY))

 

  • amitchandak's avatar
    amitchandak
    4 years ago

    doubleclick , Try like

     


    calculate( distinctcount(MainTableDE[Worker]) ,All('Calendar'),filter( MainTable,(DATEDIFF(MainTable[Start Date],EoM,DAY)>=365)),MainTable[Start Date]<=EoM,ABS(
    MainTable[End Date]>=SoM))

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    doubleclick , Try like

     


    calculate( distinctcount(MainTableDE[Worker]) ,All('Calendar'),filter( MainTable,(DATEDIFF(MainTable[Start Date],EoM,DAY)>=365)),MainTable[Start Date]<=EoM,ABS(
    MainTable[End Date]>=SoM))

    • doubleclick's avatar
      doubleclick
      Icon for Resolver I rankResolver I
      4 years ago

      Thank you so much!!!! This fixed it - Issue Resolved!

  • gotohbh's avatar
    gotohbh
    Frequent Visitor
    3 years ago

    Hello,

     

    i want transformed countx to distinctcount in this formule :

    Count lots Months 2 = VAR __Table = GROUPBY('dw V_F_ObjetQuittanceSinistre','dw V_F_ObjetQuittanceSinistre'[DateReference_AnneeMois],"__Count",COUNTX (CURRENTGROUP(),[Lots]))
    RETURN
        MAXX(__Table,[__Count])