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
Alicia_Anderson
Resolver I
Resolver I

Conditional formatting on tables vs matrix

I have conditional formatting set up on a table.  Problem:  I want to hide or gray out rows for the same Case.ID so that that information is not repeated.   In the example below, I only want to see the info shown in red one time.

Table.png

I set up a matrix as an alternative but that didn't work for me either.   It fixed the issue with not repeating the common info, but I lost the ability to do conditional formatting on columns and lost the ability to show banded rows.  Note:  I have no fields set in Values as I don't want to summarize this data.   Matrix.png

Can anyone provide me with a solution for this?  Thanks.

3 REPLIES 3
Alicia_Anderson
Resolver I
Resolver I

Thank you for responding. I was not able to get this to work. It would help me if the ranking measure was only based on Case ID field. (Let's forget about including any other fields for now.) I am most interested in determining if the Case ID matches the one above and then formatting it.

Hi @Alicia_Anderson ,

 

You cannot take the other fields from your ranking measure because measures are based in context, this means that any values that you have that will impact the calculations will change the final result.

 

In this case if you only get the Case ID field you will get for every row a ranking from 1 to N so you will not be abble to filter out the first one of each "group". Every Column you add to your table you are adding context to your calculations so this needs to be taken into account in order to have the correct values because you have several levels of interaction.

 

Making this visible with values:

 
CaseID Name Rank Based on CaseID Rank Based on CASEID and Name
1 AA 1 1
2 BB 2 1
3 CC 3 1
4 DD 4 1
5 EE 5 1
6 FF 6 1
7 FF 7 2
8 FF 8 3
9 FF 9 4

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

 

 

 
 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

Hi @Alicia_Anderson ,

 

To what I can see from your screens shots you need to identify what is the row of the specific case id and if there is repetion.

 

What I did was to create two measure, one ranking by case ID and another for condittional formatting.

 

ranking based on case ID =
RANKX (
    FILTER (
        ALLSELECTED (
            'Table'[Case ID];
            'Table'[BUG ID];
            'Table'[DA Reason];
            'Table'[DA-ID]
        );
        'Table'[Case ID] = MAX ( 'Table'[Case ID] )
    );
    CALCULATE ( MAX ( 'Table'[BUG ID] ) );
    ;
    ASC
)

Condittional Formatting = if([ranking based on case ID] = 1 ;"#000000";"#00000000")

Now use the formatting measure to do the conditonal formatting for the values you want to hide:

MFelix_0-1605874979315.png

Be aware that I have all the information in a single table if you have it in more than one table this needs to be adjusted but for that I would need to know how the model is setup.

 

To debug this and see if there are any columns you need to add or remove from the Ranking place that measure in your table visualization and then start changing the setup if the ranking gives you different Values for every row and starting at 1 at each case ID the formula is correct if you have duplicate values within the same ID then you have to change the measure.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.