cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Count Problem

Hi,

I do not know if this is possible, but I would really appreciate any help I can get. Here is the sample data set I will be using to help you understand my problem:

My goal is to compare a single fund's investors to the rest of the investors. I want to be able to show the repeat investors within different funds. To do this, I made a "count" calculation to help identify the investors. Here it is:

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

Here is what I have now:

I want to compare a single fund's investors (by using the left slicer) to the rest of the investors. This "count" calculation works perfectly, as the slicer does not affect the calculation at all. I can select a single fund and it will show me that fund's investors and how many times they repeat across the others:

But now my problem is that I want to show what funds "Louis" (for example) is invested in. When I add the fund to the next level on the matrix, I get the following:

It only shows me fund 3 for Louis (because the slicer is affecting it), but I want it to show all of the funds Louis is invested in.

I have spent so much time trying to figure this out, so any help at this point will be much appreciated.

Thank you in advance!

1 ACCEPTED SOLUTION
Microsoft Employee

To do this kind of thing, you need a separate and disconnected table for your fund list for the slicer.  Follows these steps to get the output shown below.

1.  Make a disconnected (no relationship) table called Funds

Funds = DISTINCT('Table'[Fund])

2.  Use it in your slicer.

3. Use the following two measures in your matrix visual

Selected Fund Count = CALCULATE(COUNTROWS('Table'), TREATAS(VALUES(Funds[Fund]), 'Table'[Fund]))

Fund Count = if(ISBLANK([Selected Fund Count]), BLANK(), COUNTROWS('Table'))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

6 REPLIES 6
Super User

Hi,

Maybe i am not clear about your requirement but why don't you just create a slicer of Investor and select Louis there.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Microsoft Employee

To do this kind of thing, you need a separate and disconnected table for your fund list for the slicer.  Follows these steps to get the output shown below.

1.  Make a disconnected (no relationship) table called Funds

Funds = DISTINCT('Table'[Fund])

2.  Use it in your slicer.

3. Use the following two measures in your matrix visual

Selected Fund Count = CALCULATE(COUNTROWS('Table'), TREATAS(VALUES(Funds[Fund]), 'Table'[Fund]))

Fund Count = if(ISBLANK([Selected Fund Count]), BLANK(), COUNTROWS('Table'))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Anonymous
Not applicable

Also, for my real model, I have other values that need to be in the matrix. When I add those values into the matrix, all of the Investor Names pop up. Here is a more accurate depiction of my data:

When I use your formulas, it works until I add committed capital and descriptions onto the values in the matrix:

Please let me know if there is a way to solve this. Thank you.

Microsoft Employee

You don't need to have that measure in the visual.  And to get your desired result (adding other measures w/o adding rows), you can use the Selected Fund Count measure to filter  the matrix visual.  Just highlight the matrix, and drag the measure into the Filters On This Visual in the Filters panel.  Set it to "Is Not Blank" and Apply Filter.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Anonymous
Not applicable

You are a life-saver.

I have been working on this forever. Thank you so much.

Anonymous
Not applicable

Thank you so much. Do I need to have the "Selected Fund Count" in the matrix in order for this to work?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors