Count of values in a column

[Anonymous]

I've created a table from the fields Source and Preparer. If I want to see those sources where there is only one preparer what's is the best way to do it 

Like the Sources "MN", "RT" and "PO". 

Please note that this is a table I created from my dataset, so if I take the count of Source as the third column that is going to be a huge number. 

Source	Preparer
AB	June
AB	Jane
MN	June
SS	Kim
SS	Lee
SS	Jim
RT	Kim
PO	June
PO	June
RT	Kim

The outcome should be like 

Source	Preparer
MN	June
RT	Kim
PO	June

Thanks in advance!

[smpa01]

@Anonymous  a DAX Measure solution

newMeasure = 
CALCULATE (
    DISTINCTCOUNT ( 'Table 1'[Preparer] ),
    FILTER (
        GROUPBY ( 'Table 1', 'Table 1'[Source], 'Table 1'[Preparer] ),
        CALCULATE (
            DISTINCTCOUNT ( 'Table 1'[Preparer] ),
            ALLEXCEPT ( 'Table 1', 'Table 1'[Source] )
        ) = 1
    )
)

[KNP]

Hi @Anonymous, 

You could try this Power Query Solution.

Let me know if this helps or if you have further questions.

[Anonymous]

@KNP  thanks for your response,

There is only one Source in Table2 from your solution, but I need 3 sources i.e. "MN", "RT" and "PO" where there is only one preparer for these sources 

[KNP]

@Anonymous - I understand now.

I've updated the PBIX file. Please have another look.

Basically this line...

#"Grouped Rows" = Table.Group(Source, {"Source"}, {{"Count", each Table.RowCount(List.Distinct(_[Preparer])), Int64.Type}, {"All", each _, type table [Source=nullable text, Preparer=nullable text]}}),

Gets the distinct count.

[Anonymous]

@KNP  thanks,

But is there any way to use calculated columns as I've to apply this solution to more fields and that too some are in other tables?  

[KNP · Accepted solution]

@Anonymous you could apply the Power Query solution to other tables/fields. It's a little difficult to answer completely without a complete picture of your entire scenario.

For a DAX solution, you could do something with this...

Table 2 =
FILTER (
    SUMMARIZECOLUMNS (
        'Table'[Source],
        "Count Distinct", DISTINCTCOUNT ( 'Table'[Preparer] )
    ),
    [Count Distinct] = 1
)

Note, this is not a calculated column, this was added as a table via the modelling tab >> New table option but the pattern could equally be added into a calculated column or measure. 

I would need more time than I can spend at the moment, maybe someone with more DAX skills could adapt this for you.

[CNENFRNL]

Calculated column

Count = COUNTROWS( FILTER( Table1, Table1[Source] = EARLIER( Table1[Source] ) ) )

 

[Anonymous]

Hi @CNENFRNL  thanks for your reply

But when I apply this in my dataset I got a huge value for the sources MN, RT and PO. I think based on your formula I get the total count of Sources in my dataset, 

I've updated my question for a better understanding 

[Anonymous]

I've applied the formula to the updated dataset and now I get this count

Even though the Sources RT and PO has only one preparer, the count shows 2. 

[aj1973]

Hi @Anonymous 

is this good!?

It is a measure though, not an added column

[Anonymous]

@aj1973 thanks,

If I filter on the basis of count I'll get the source "MN", but in fact, there is "RT" and "PO" where there is only one preparer. even though they are occurring multiple times in the record, still RT and PO only contains 1 preparer 

[aj1973]

@Anonymous 

Can you screen shot or represent the out come you want to see?

[Anonymous]

@aj1973  updated my question with the outcome