Count Of Drivers

[Suhel_Ansari]

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 as well. Please assist. Thanks

[mark_endicott · Accepted solution]

@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 · Accepted solution]

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.

[Suhel_Ansari]

@ajaybabuinturi  - It's wokring as expceted. Thanks

[speedramps]

Everyone can go to the effort of creating examples with dummy data ... even you.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

[mark_endicott · Accepted solution]

@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!

[Cookistador]

Hi @Suhel_Ansari 

The following measure should return what you need

Contracts Count per Driver =
COUNTROWS ( FACT_CONTRACTS )

And this is for your calculated column

Driver Contracts Count=
CALCULATE (
COUNTROWS ( FACT_CONTRACTS ),
RELATEDTABLE ( FACT_CONTRACTS )
)

[speedramps]

I want to help you but your description is too vague. Please write it again.

Your relationship diagram does not show what fileds are related and there is no example data.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[Suhel_Ansari]

@speedramps I can't share the PBI file as it has some Sensitive data 

[ajaybabuinturi]

Hi @Suhel_Ansari, 
Could you please share the .pbix file with some sample data to understand the relationship b/w the two tables.  

[Cookistador]

Hi @Suhel_Ansari 

In which context would like to achieve that? Also in a table ?

As you mentionned 2 tables, you have to use related

So the following code should help you

Driver Count =
COUNTROWS ( RELATEDTABLE ( 'Contracts' ) )

Just add it in the contract table

If it is not what you are trying to achieve, share with us a sample of your 2 tables 🙂

[Suhel_Ansari]

it's work for Calcuated colum but we need the measure as well. Thanks 

[mark_endicott]

@Suhel_Ansari - if this is a response to me. 

You can use the same code, it will work in both a calculated column and a measure. 

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

[Suhel_Ansari]

hi @Cookistador  I need measure and calcualted columns as well

[ajaybabuinturi]

@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]

Hi All,

This can help you people, it's very simple 

[ajaybabuinturi]

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.

[Suhel_Ansari]

@burakkaragoz