Need the conditional formatting and blanks for a particular row as blank in table visual

[Lahari]

Hi @AIB

Please help me in solving this issue. i have tried many ways but i am not able to solve this issue. can you please help me to solve this issue

i have a scenario with a table with users and roles and in that i need to compare the users and roles for two different dates like date1 and date2. i need to use a table visual where columns as date1_users, date1_roles,date2_users,date2_roles. Now i have to make blank when the user and role which is not present in date1 but present in date2. similarly when the user and role is present in date2 but not present in date1 then i need to make date2_role and date2_user as blanks. 

Let me take an example data

User	Roles	Dates
A	A1	07/12/2023
A	A2	07/12/2023
A	A3	07/12/2023
B	B1	07/12/2023
B	B2	07/12/2023
B	B3	07/12/2023
B	B4	07/12/2023
C	C1	07/12/2023
C	C2	07/12/2023
C	C3	07/12/2023
D	D1	07/12/2023
A	A1	07/13/2023
A	A2	07/13/2023
B	B2	07/13/2023
B	B3	07/13/2023
B	B4	07/13/2023
C	C1	07/13/2023
C	C2	07/13/2023
E	E1	07/13/2023

i need the output in the table visual as

Date1 - 07/12/2023 Users	Date1 - 07/12/2023 Roles	Date2 - 07/13/2023 Users	Date2 - 07/13/2023 Roles	Date1 User	Date2 User
A(total)	3	A(total)	2
A	A1	A	A1	1	1
A	A2	A	A2	1	1
A	A3			1	0
B(total)	4	B(total)	3
B	B1			1	0
B	B2	B	B2	1	1
B	B3	B	B3	1	1
B	B4	B	B4	1	1
C(total)	3	C(total)	2
C	C1	C	C1	1	1
C	C2	C	C2	1	1
C	C3			1	0
D(total)	1	D(total)	0
D	D1			1	0
E(total)	0	E(total)	1
		E	E1	0	1

and i need to make conditional formatting as well if the row which is blank then it should be highlighted with a color and the user need to export the same excel file with the conditional formatting from power bi service to desktop.(that is the user need to download the above table visual as same in excel format with color coding format)

If it is possible to get this output in power bi please hlep me to solve this issue. Thanks in advance and i have tried many ways but i am not able to solve this issue, please help me to sovle this issue. 

[Lahari]

Hi @Sahir_Maharaj 

Thanks for replying back and

i have created measures as you have mentioned. I need list of users names based on DateUsers - measures which give me count but not list of users. how can i achieve that please help me on that.

[Sahir_Maharaj]

Hello @Lahari,

Can you please try this:

1. Load your data into Power BI, ensuring you have columns for User, Roles, and Dates.

2. Create a new table visual.

3. Add the following fields to the visual:

   1. Dates column in the "Rows" section.
   2. User column in the "Values" section, and set the aggregation to "Distinct Count."
   3. Roles column in the "Values" section, and set the aggregation to "Distinct Count."

4. Add a conditional formatting rule to highlight blank values in the table visual. To do this:

   1. Click on the table visual.
   2. In the "Visualizations" pane, go to the "Conditional formatting" section.
   3. Select the field that represents the values you want to format (e.g., User or Roles).
   4. Choose the formatting options, such as font color or background color, for the blank values.

5. To export the table visual with conditional formatting from Power BI Service to desktop, follow these steps:

   1. Publish the Power BI report to the Power BI Service.
   2. Open the report in the Power BI Service.
   3. Click on the "File" menu and select "Export > Export to Excel."

Should you require further assistance, please do not hesitate to reach out to me.

[Lahari]

Hi @Sahir_Maharaj 

Thanks for your reply. i have tried what you have explained me but i need in the format which i have shown you on second table. i need to create a table visual where i need to merge two tables, each table is from selected date which is from a slicer and here i am two calendar dates tables and each silcer date1 is coming from calendar table1 and slicer date2 is coming from another calendar table2. Now i need to filter the users and roles based on date1 and date2 and i need to merge those 2 tables with full outer join by this we can achieve but i dont know how to write dax for this approach. can you please helpme in writing dax for this type of approach. If any further information is needed please let me know. once again thanks for your reply.

[Sahir_Maharaj]

Hello @Lahari,

Thank you for your response. Can you please try this:

1. Create two calendar tables: Create two separate calendar tables, Calendar1 and Calendar2, each with a single Date column.

2. Create relationships: Establish relationships between the Dates column in the User table and the respective Date columns in Calendar1 and Calendar2.

3. Create a measure for Date1 Users:

Date1Users = CALCULATE(DISTINCTCOUNT(User[User]), USERELATIONSHIP(User[Dates], Calendar1[Date]))

4. Create a measure for Date1 Roles:

Date1Roles = CALCULATE(DISTINCTCOUNT(User[Roles]), USERELATIONSHIP(User[Dates], Calendar1[Date]))

5. Create a measure for Date2 Users:

Date2Users = CALCULATE(DISTINCTCOUNT(User[User]), USERELATIONSHIP(User[Dates], Calendar2[Date]))

6. Create a measure for Date2 Roles:

Date2Roles = CALCULATE(DISTINCTCOUNT(User[Roles]), USERELATIONSHIP(User[Dates], Calendar2[Date]))

Should you require further assistance, please do not hesitate to reach out to me.

[Lahari]

Hi @Sahir_Maharaj 

I am sharing you the screen shot where i have done the conditional formatting of the column based on the measure created but when i export the file in excel it will shows the users in prior who are 0 in measure but color format cant be exported in excel. so i want to make a virtual column to replace values based on condition of 0 and 1 of measure. 

in excel i am getting like this

i need to make manually blank for the A column and B column for those 0 in prior that should not be done and it should be done by power bi itself and that how can i achieve this please help me on this.

[Lahari]

Hi @Sahir_Maharaj 

Thanks for replying back and

i have created measures as you have mentioned. I need list of users names based on DateUsers - measures which give me count but not list of users. how can i achieve that please help me on that.