Format value based on selection

[Oros]

 Hello,
 
What would be the correct measure to use, if I would like to highlight from the TOP N products only the ones that were sold by the selected salesperson?  I have dropdown selector for TOP N, as well another dropdown selector for Salesperson.
Thanks.

[Ritaf1983 · Accepted solution]

Hi @Oros 
You should create a names table that is not connected to the transactions table :

and then create Dax with crossjoin inside for marker's color :

color =

VAR SelectedNames = VALUES(Names[Name])

VAR ProductsFromSelectedNames =

    DISTINCT(

        FILTER(

            CROSSJOIN(Names, fruits),

            'fruits'[Name] IN SelectedNames

        )

    )

VAR CurrentProduct = SELECTEDVALUE(fruits[Product])

RETURN

    IF(

        CurrentProduct IN SELECTCOLUMNS(ProductsFromSelectedNames, "Product", fruits[Product]),

    "Yellow",

        0

    )

Use names table for the slicer:

Use a measure that we created for the conditional formatting

Result:

Will work with top n logic in the same way.

pbix is attached

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

[v-jiewu-msft · Accepted solution]

Hi @Oros ,

Based on the description, the method Ritaf1983 provided should be helpful

Besides, try using the following DAX formula to filter the top n product.

Product Rank = RANKX(ALL('Table'), [Total number], , DESC, Dense)

TopN filter = 
   VAR SelectedTopN = SELECTEDVALUE('Table top n'[TopN])
   RETURN
       IF([Product Rank] <= SelectedTopN, 1, 0)

Drag the measure to the Filters pane and set the show item is 1.

Then, drag the sales person to the slicer visual and select the person.

Best Regards,

Wisdom Wu

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

[v-jiewu-msft · Accepted solution]

Hi @Oros ,

Based on the description, the method Ritaf1983 provided should be helpful

Besides, try using the following DAX formula to filter the top n product.

Product Rank = RANKX(ALL('Table'), [Total number], , DESC, Dense)

TopN filter = 
   VAR SelectedTopN = SELECTEDVALUE('Table top n'[TopN])
   RETURN
       IF([Product Rank] <= SelectedTopN, 1, 0)

Drag the measure to the Filters pane and set the show item is 1.

Then, drag the sales person to the slicer visual and select the person.

Best Regards,

Wisdom Wu

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

[Oros · Accepted solution]

Hi @v-jiewu-msft,

Thank you so much for helping.  Your solution works as well! 

[Ritaf1983 · Accepted solution]

Hi @Oros 
You should create a names table that is not connected to the transactions table :

and then create Dax with crossjoin inside for marker's color :

color =

VAR SelectedNames = VALUES(Names[Name])

VAR ProductsFromSelectedNames =

    DISTINCT(

        FILTER(

            CROSSJOIN(Names, fruits),

            'fruits'[Name] IN SelectedNames

        )

    )

VAR CurrentProduct = SELECTEDVALUE(fruits[Product])

RETURN

    IF(

        CurrentProduct IN SELECTCOLUMNS(ProductsFromSelectedNames, "Product", fruits[Product]),

    "Yellow",

        0

    )

Use names table for the slicer:

Use a measure that we created for the conditional formatting

Result:

Will work with top n logic in the same way.

pbix is attached

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

[Oros]

Hi @Ritaf1983,

Thank you so much for helping.  Your solution works!

[Ritaf1983]

Happy to help 🌷

[danextian]

You will need to use a disconnected table  containing a column of person name. You can create one using DAX

PersonsDisconnected = DISTINCT ( DataTable[Person] )

And then a conditional formatting measure

IF (
    NOT (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( DataTable ),
                KEEPFILTERS ( TREATAS ( PersonsDisconnected, DataTable[Person] ) )
            )
        )
    ),
    "yellow"
)

From the conditional formatting options, select field value and then this measure.

[lbendlin]

Most likely this will require disconnected tables.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...