Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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: 

cnormand_0-1594157092337.png

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: 

asdfh4tb.png

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: 

1.png

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: 

2.png

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
mahoneypat
Employee
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'))
 
funds.png
 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Employee
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'))
 
funds.png
 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@mahoneypat 

 

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:

cnormand_0-1596478088033.png

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

cnormand_1-1596478382481.png

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

 

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.

 

filtervisual.png

 

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@mahoneypat ,

 

You are a life-saver.

 

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

Anonymous
Not applicable

@mahoneypat 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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