Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kevinsray
Helper II
Helper II

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?

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @kevinsray ,

According to your description, I create a sample.

top 100 sales products table:

vkalyjmsft_0-1659347679094.png

SalesTable:

vkalyjmsft_1-1659347733527.png

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.

vkalyjmsft_2-1659348395376.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

Hi @kevinsray ,

According to your description, I create a sample.

top 100 sales products table:

vkalyjmsft_0-1659347679094.png

SalesTable:

vkalyjmsft_1-1659347733527.png

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.

vkalyjmsft_2-1659348395376.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

danextian
Super User
Super User

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). 






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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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" )

 






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

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Hi,
Thank you.
Yeah, this won`t do what I need to do.
As the initial data is coming from an SQL query which queries what the top 100 sold products and what I need to add to that, is when the slicer for a customer is selected, the CF shows which of those 100 they have purchased.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.