Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Matrix: add frequency and hide when frequency <7

I made a matrix to show the mean score per item (EA-PI column) for each of the genders (male, female, other).

Matrix PBI.JPG

 I am now looking for a way to show the frequency of the genders as well. So for example to make the header look like this: Male (n=15). I tried adding a tooltip but then the frequency only shows up if you hover over the scores, while I want to see it when I hover over the header, and I'd rather not have a tooltip on the mean scores.

 

I would also like to hide columns if their frequency is below 7 participants. For all my other visuals I used the following line of code:

IS Valid Group = IF(CALCULATE(DISTINCTCOUNT(OrigineleData[Respondent]) > 7; ALLSELECTED(OrigineleData)) = TRUE(); 1; 2)

and then used a filter on the visuals so they only show data when IS Valid Group = 1. However, this does not seem to work on the matrix. 

 

Thank you for your help!

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

We can try to create a measure as value field of matrix to meet your requirement:

 

Measure = If([IsValidGroup] = 1, Average('Table'[Value]), BLANK())

 

2.jpg3.jpg

(s3=7 was hidden when frequence reduce)


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
v-lid-msft
Community Support
Community Support

Hi @Anonymous ,

 

When use Allselected function, all the cells will return same result, Could you please try the following measure? 

 

IS Valid Group =
IF ( DISTINCTCOUNT ( OrigineleData[Respondent] ) > 7; 1; 2 )

 

If it doesn't meet your requirement, Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to OneDrive For Business and share the link here.

Please don't contain any Confidential Information or Real data in your reply.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you for your reply @v-lid-msft, unfortunately your formula doesn't seem to work either. I uploaded my report on one drive for business here.

 

All data in this report are fake. The matrix I was referring to is on page "Details gender".

Hi @Anonymous ,

 

Based on my research with sample model you have shared, we find the each row (items column) has the same Unique Response ID from 0 to 186, so the distinct count for each cells in matrix are large than 7, we think it is the reason why measure does not work.

 

For example, when items = "Persoonlijk Welzijn" and S3 = 1, the will be 28 distinct value in the “Respondant" column of origin table, does the 28 means the desire frequency?

 

4.jpg5.jpg6.jpg7.jpg


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-lid-msft thank you for your reply!

 

28 is indeed the frequency I was talking about. But how can I hide or empty the cell "Persoonlijk welzijn x s3=1" if the frequency would be below 7? 

Hi @Anonymous ,

 

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Anonymous ,

 

We can try to create a measure as value field of matrix to meet your requirement:

 

Measure = If([IsValidGroup] = 1, Average('Table'[Value]), BLANK())

 

2.jpg3.jpg

(s3=7 was hidden when frequence reduce)


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Sorry for my late reply.

 

Your solution seems to work when you do it, the images you posted are indeed what I'm trying to achieve. I edited this reply because at first I got error messages using your formula. But I got that to work now.

 

Only problem is that the entire matrix goes blank now. And I can't make it appear again using this measure, so something is not right. Why does it work when you do it? Can you send me the file you did this in?

 

Best regards,

Jade

 

Hi @Anonymous ,

 

Have you tried to change the [IS Valid Group] measure to following one we shared before?

 

IS Valid Group = 
IF ( DISTINCTCOUNT ( OrigineleData[Respondent] ) > 7,1, 2 )

 

Please also change the background color of measure, because replace the value, condition format will reset and I remember the text color of your matrix is white.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

It's working! It was the white background indeed... I didn't realize that the conditional formatting would disappear. 

I didn't even have to change the measure.

 

Thank you so much! 🙂 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.