Forum Discussion

Suhel_Ansari's avatar
Suhel_Ansari
Helper V
1 year ago
Solved

Count Of Drivers

Hi Team, I have 2 columns coming from 2 different tables called Diver and Contact as seen in follwing table, i want to calculate the Count of driver occured, I want a Measure and calculated column a...
  • mark_endicott's avatar
    1 year ago

    Suhel_Ansari - this should work for both a column and a measure. Just replace Table with your table name:

     

    CALCULATE(COUNTROWS( Table), ALLEXCEPT(Table, Table[Driver Name] ))

     

    If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

     
  • ajaybabuinturi's avatar
    1 year ago

    Hi Suhel_Ansari 
    Create Calculated column in the Fact_Contracts table

    Driver Contract Count = 
    CALCULATE(COUNTROWS(Fact_Contracts), ALLEXCEPT(Fact_Contracts, Fact_Contracts[Driver_key]))

    and create measure in the Dim_Driver table

    Driver Count(M) = 
    IF(HASONEVALUE(Fact_Contracts[Driver_key]),
        //True Result
        CALCULATE(COUNTROWS(Fact_Contracts),
            FILTER(ALL(Fact_Contracts),Fact_Contracts[Driver_key] = MAX(Fact_Contracts[Driver_key]))),
        //False Result
        SUMX(VALUES(Fact_Contracts[Driver_key]), CALCULATE(COUNTROWS(Fact_Contracts)))
    )

    I am also attaching a .pbix file for your reference.
    https://drive.google.com/file/d/1MHPeKgVecPhIaFDpby81DQg8G2nbVrgZ/view?usp=sharing

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.