Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a requirement of showing alternate row colour in a table after every 7 rows. Below is an example . For the first 7 rows i would like to have background color is grey and the next 7 rows it is blue and again for next 7 rows it is grey so on... I have explored conditional formating but am not able to get in to there. Request your expert views in solving this.
S. No | Type | Qty |
1 | Opening | 10 |
1 | Received | 5 |
1 | AAA | 0 |
1 | BBB | 0 |
1 | CCC | 0 |
1 | Sold | 5 |
1 | Closing | 10 |
2 | Opening | 15 |
2 | Received | 5 |
2 | AAA | 0 |
2 | BBB | 0 |
2 | CCC | 0 |
2 | Sold | 5 |
2 | Closing | 15 |
3 | Opening | 10 |
3 | Received | 5 |
3 | AAA | 0 |
3 | BBB | 0 |
3 | CCC | 0 |
3 | Sold | 5 |
3 | Closing | 10 |
4 | Opening | 15 |
4 | Received | 5 |
4 | AAA | 0 |
4 | BBB | 0 |
4 | CCC | 0 |
4 | Sold | 5 |
4 | Closing | 15 |
Solved! Go to Solution.
hi, @sivarammoto
If each [S.No] in visual all have 7 rows, If so, just try this way:
Use RANKX Function to add a rank measure for [S.No], and then use IsEven Function nested it.
EvenOdd = IF(ISEVEN(RANKX(ALLSELECTED('Table'),CALCULATE(SELECTEDVALUE('Table'[S. No])),,ASC,Dense)) = TRUE(),1,0)
Measure = IF(ISEVEN(INT((RANKX(ALLSELECTED('Table'),CALCULATE(SELECTEDVALUE('Table'[S. No])&SELECTEDVALUE('Table'[Type])),,ASC,Dense)-1)/7)) = TRUE(),1,0)
One way to solve this is with a measure:
Hi Andy,
Thanks for your quick reply..
S.No which i mentioned in my example was dummy data. In real scenario S.No will be like "ABC7089010". So "IsEven" function will not be useful. I have also tried "Mod" function but that is not working. I am exploring permanent solution where if i add new column also it should work instead of applying conditional format on each columnn seperately
hi, @sivarammoto
If each [S.No] in visual all have 7 rows, If so, just try this way:
Use RANKX Function to add a rank measure for [S.No], and then use IsEven Function nested it.
EvenOdd = IF(ISEVEN(RANKX(ALLSELECTED('Table'),CALCULATE(SELECTEDVALUE('Table'[S. No])),,ASC,Dense)) = TRUE(),1,0)
Measure = IF(ISEVEN(INT((RANKX(ALLSELECTED('Table'),CALCULATE(SELECTEDVALUE('Table'[S. No])&SELECTEDVALUE('Table'[Type])),,ASC,Dense)-1)/7)) = TRUE(),1,0)
Thanks Lin,
Your solution is working fine. However in this case i have to apply conditional formatting for every column, is there any way that i can create a variable and assign that variable at row level so that even if i add new column also it works .
Regards
Siva
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |