Adding a count column based on values from another column

[akkitek]

Hi Everyone,
I have a data model with few different tables directly or indirectly connected each other. I have built a table visual with three columns, each coming from a different dataset. Client Number from DIM_Client, Project code from Dim_Projects and Engagement Divisons from Fact_fin_data. I want to add a fourth column using a calculated measure or column to get a count of the number of divisions for each client and then use that to identify clients using 2 or more divisions. I've tried the following measures but they don't seem to work. 

Division Count per Client = 
CALCULATE(
    COUNTROWS(Fact_fin_data),
    FILTER(
        ALL(DIM_Client[Client Name]),
        COUNTROWS(
            FILTER(
                RELATEDTABLE(Dim_Projects),
                RELATEDTABLE(Fact_fin_data)
            )
        ) > 0
    )
)

Should I be creating a calculated column since we want to use this column further? 

 I can then look at the number of clients with multiple divisions(2 or more divisions) as a % of the total client base. 
Here's the sample file
Any help is appreciated. Thanks!

[v-xuxinyi-msft · Accepted solution]

Hi @akkitek 

Thanks for the reply from @lbendlin .

If I understand correctly, you are trying to calculate how many Engagement Divisions there are per client.

What are your calculation criteria? Do you not double count the same division? I'm a little confused about the expected result you are giving, if I understand you correctly, then the expected result for A-00103 should be 2, not 3? 

Since I found that the FACT_Fin_Data_by_Mo table contains the three columns used in the visualization chart, why do you have to use three tables instead of the FACT_Fin_Data_by_Mo table? Here's what I've done so far, I used the three columns from the FACT_Fin_Data_by_Mo table and then I created the following measure:

of divisions per client = CALCULATE(DISTINCTCOUNT(FACT_Fin_Data_by_Mo[Engagement Division]), ALLEXCEPT(FACT_Fin_Data_by_Mo, FACT_Fin_Data_by_Mo[Client Number]))

Output:

You can also use a calculated column if you want, as shown here:

Please feel free to correct me if I have misunderstood you.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[akkitek]

Thanks for the help @v-xuxinyi-msft !

[akkitek]

@v-xuxinyi-msft Thank you so much for your response. 
The calculation criteria is that every client can have multiple projects and each project can have different or same divisions. So, although there are 2 distinct divisions the expected result for A-00103 should be 3 since there are 3 different projects. 

Based on your calculated column, I tried changing the "distinctcount" function to "count" but it doesn't work. Should I be adding another layer to this column calculation? 

Yes, these columns come from one dataset so its doable. But would this also work if they were in different tables?

Thanks! really appreciate the help:)

[lbendlin]

you can create a calculated column but only if the result cannot be influenced by users interacting with your report. In those cases you would need a measure.

In which way do you want to use that column further?

[akkitek]

Thanks for your reply @lbendlin ,
What I meant by using the column further was, after we count the number of divisions per client we need to use that column to flag how many clients have more than 2 divisions.

[lbendlin]

Depending on how you expect user interaction with your report to impact the results you can still choose to implement this either as a calculated column or a measure.