Forum Discussion
Column reflecting Conditional Formatting
Hi,
Would really appreciate some help.
Situation:
Power BI report page has a slicer to get CustomerCompanyName.
There is a table visual which shows top 100 sales products which is generated from a SQL query.
On this table visual there is a conditional format that changes background color if the selected customercompanyname has purchased any of the top100 items in the last 6 months.
However, when you export the data to CSV, the conditional formatting is lost and unable to tell what the customer purchased.
Is there a way to create a purchased Y/N column based on whether the conditional formatting is active or not?
Any other methods of doing this?
Hi kevinsray ,
According to your description, I create a sample.
top 100 sales products table:
SalesTable:
In my sample, in the last 6 month(2022/2/2-2022/8/1), only Company3 and Company4 has products in the top 100 sales products table, so the result should be N for Company1 and Company2, Y for Company3 and Company4. Here's my solution, create a measure.
Measure = IF ( COUNTROWS ( FILTER ( ALL ( 'SalesTable' ), 'SalesTable'[CustomerCompanyName] = MAX ( 'SalesTable'[CustomerCompanyName] ) && 'SalesTable'[Product] IN SELECTCOLUMNS ( 'top 100 sales products', "TOP", 'top 100 sales products'[top 100 sales products] ) && 'SalesTable'[Date] IN DATESINPERIOD ( 'Date'[Date], TODAY (), -6, MONTH ) ) ) > 0, "Y", "N" )Get the result. In my understanding, the CustomerCompanyName slicer only for filtering the visual, but have no effect on the result of the measure.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi kevinsray ,
You cannot create another column based on conditional formatting. You can, however, create another measure to return Y/N based on the value on another measure and add that to your visual so it gets exported as well. I'm pretty sure your conditional formatting is based on some kind of aggregation (count of rows in a column, sume of a values, etc).
Thank you.
Yes, the CF is just saying that if the count of the Qty from the sales table is more than 0, set background to yellow.
I would be very interested to figure out how to create such a measure.
I understand measures, but not how to work in selections from other visuals into them.Try something similar to below
Y/N = IF ( [count of something] > 0, "Y", "N" )
Hi kevinsray ,
According to your description, I create a sample.
top 100 sales products table:
SalesTable:
In my sample, in the last 6 month(2022/2/2-2022/8/1), only Company3 and Company4 has products in the top 100 sales products table, so the result should be N for Company1 and Company2, Y for Company3 and Company4. Here's my solution, create a measure.
Measure = IF ( COUNTROWS ( FILTER ( ALL ( 'SalesTable' ), 'SalesTable'[CustomerCompanyName] = MAX ( 'SalesTable'[CustomerCompanyName] ) && 'SalesTable'[Product] IN SELECTCOLUMNS ( 'top 100 sales products', "TOP", 'top 100 sales products'[top 100 sales products] ) && 'SalesTable'[Date] IN DATESINPERIOD ( 'Date'[Date], TODAY (), -6, MONTH ) ) ) > 0, "Y", "N" )Get the result. In my understanding, the CustomerCompanyName slicer only for filtering the visual, but have no effect on the result of the measure.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
