Forum Discussion

kevinsray's avatar
kevinsray
Helper II
4 years ago
Solved

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 ...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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 _ kalyj

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

danextian's avatar
danextian
Super User
4 years ago

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

  • kevinsray's avatar
    kevinsray
    Helper II
    4 years ago

    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.

    • danextian's avatar
      danextian
      Super User
      4 years ago

      Try something similar to below

      Y/N =
IF ( [count of something] > 0, "Y", "N" )

       

      • kevinsray's avatar
        kevinsray
        Helper II
        4 years ago

        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.