Count Problem

[Anonymous]

Hi, 

This problem might not be possible to resolve, but I would really appreciate any help I can get. 

Here is a sample dataset:

My goal is to compare a single fund's investors to all the rest of the investors. If I select one of the funds, I want to see if that fund's investors match up with any other investors in other funds. So, I made a count formula to show how many times each investor shows up in all funds regardless if I use a slicer to affect the table. Here is the formula: 

Count =
IF (
    ISINSCOPE ( 'Table'[Investor] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Fund] ),
        ALL ( 'Table'[Fund] )
    ),
    CALCULATE (
        COUNT ( 'Table'[Fund] )
    )
)

I now have the following:

And, I am able to use the slicer to show only a certain fund's investors, while still showing the count for each investor:

But, my problem is I need to see what fund each investor is in. When I add the fund level to the matrix, it does not show me all of the funds: 

In this picture above, I want it to show me that "Louis" is in Fund 1, 2, 3, and 4. But instead, it only shows me fund 3.

I have been trying to solve this for so long, but I can not figure out how. Any help would be greatly appreciated. 

Thank you in advance!

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Alexander76877's way is good. I bulid a new table to make a slicer as well, you may try my way.

I build a table like yours to have a test.

Build a Slicer Table with distinct values in Fund Column.

Slicer = DISTINCT('Table'[Fund])

Then build a Slicer.

I build a measure to achieve your goal.

Count =

VAR _Funds =

VALUES ( 'Slicer'[Fund] )

VAR _Investorsselected =

CALCULATETABLE ( VALUES ( 'Table'[Investor] ), 'Table'[Fund] IN _Funds )

VAR _True =

CALCULATE ( DISTINCTCOUNT ( 'Table'[Fund] ) )

VAR _False =

CALCULATE (

COUNT ( 'Table'[Fund] ),

FILTER ( 'Table', 'Table'[Fund] IN _Funds )

)

RETURN

IF (

ISINSCOPE ( 'Table'[Investor] )

&& MAX ( 'Table'[Investor] ) IN _Investorsselected,

_True,

_False

)

Result:

You can download the pbix file from this link: Count Problem

Best Regards,

Rico Zhou

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

[Anonymous]

Thank you @v-rzhou-msft !

This worked as well. 

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your onedrive business.

Best Regards,

Rico Zhou

[Alexander76877]

Hi, your problem does have a solution 🙂

You must not use your original fund table for the slicer. Instead, create a new, independant table (not related in the model)

Funds = VALUES('Table'[Fund])

It is a simple table with one column and 3 rows let´s say. If you now create a slicer based on this table, you can select a single fund but this will - of course - not affect the other table in any way. 

Now you need to create a measure that takes into account what you selected in the slicer table:

Count =

VAR _funds = VALUES(Funds[Fund])

RETURN CALCULATE(COUNT('Table'[Fund]), 'Table'[Fund] IN _funds)

it counts only funds of people who are at least in the selected fund.

If this solves your problem please accept as solution to close the ticket.

Alexander

[Anonymous]

Hi @Alexander76877 ,

I am having problems with the measure. It wont let me put RETURN after VALUES(Funds[Fund]).

[Alexander76877]

Hi, the complete code for the measure is

Count =
VAR   _funds = VALUES(Funds[Fund])
RETURN CALCULATE(COUNT('Table'[Fund]), 'Table'[Fund] IN _funds) and there´s a space between "VAR" and "_funds".

So the code starts with "Count", not with "VAR".

I use the underscore to name local variables. Just copy paste the whole code.

Alexander

[Anonymous]

Hi @Alexander76877 , 

That is still not working.

Here is the code: 

And here is the result: 

It works when you drop the names down, but the count is wrong:

[Alexander76877]

Try (the whole thing is the formula):

----------------------

Count =

VAR _funds = VALUES(Funds[Fund])

VAR _investorsselected = CALCULATETABLE(

    VALUES('Table'[Investor]),

    'Table'[Fund] IN _funds

)

RETURN

SUMX(

    VALUES('Table'[Investor]),

    IF(

        'Table'[Investor] IN _investorsselected,

        CALCULATE(COUNT('Table'[Fund])),

        BLANK()
    )   

)

---------------------

If this still is not the correct value you expect please explain more in detail what you expect the value to calculate - I am not sure what you want to see.

Alexander