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...
Need Help
  • mark_endicott's avatar
    mark_endicott
    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
    ajaybabuinturi
    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.

ajaybabuinturi's avatar
ajaybabuinturi
Super User
1 year ago

Suhel_Ansari the image doesn't make any sense as because we can't understand on what column the relation established. Could you please make a some sample data in excel with required columns and some rows of data and mention the relation ship column b/w the tables

  • Suhel_Ansari's avatar
    Suhel_Ansari
    Helper V
    1 year ago

    Hi All,

     

     

     

    This can help you people, it's very simple 

    • ajaybabuinturi's avatar
      ajaybabuinturi
      Super User
      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.