Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Formatting rows unique to only one table visual

Hello everyone, I am new to DAX and I have been struggling to find a way to format rows of my table visuals, where I would like to highlight rows unique to each table visual. I have three visuals on one page, each one is being sliced by one slicer on the ID 'BM number'. The table visuals have columns 'Commodity number', 'Ingredients' and 'Weight' (similar to the data table below but no BM number column).  


Here is an example of my data:

 

I am trying to achieve a result that looks like this:

 

This is what I have so far, but I am not sure how to reference a column of a table variable:

IsUniqueInVisual =
VAR SlicerValue1 = SELECTEDVALUE('BOM ALL (Dec 2023)'[BM number])
VAR SlicerValue2 = SELECTEDVALUE('BOM ALL (Dec 2023) (2)'[BM number])
VAR SlicerValue3 = SELECTEDVALUE('BOM ALL (Dec 2023) (3)'[BM number])

VAR Table1 = FILTER(
    'BOM ALL (Dec 2023)',
    'BOM ALL (Dec 2023)'[BM number] = SlicerValue1
)
VAR Table2 = FILTER(
    'BOM ALL (Dec 2023) (2)',
    'BOM ALL (Dec 2023) (2)'[BM number] = SlicerValue2
)
VAR Table3 = FILTER(
    'BOM ALL (Dec 2023) (3)',
    'BOM ALL (Dec 2023) (3)'[BM number] = SlicerValue3
)

VAR OnlyInTable1 = EXCEPT(Table1, UNION(Table2, Table3))
VAR OnlyInTable2 = EXCEPT(Table2, UNION(Table1, Table3))
VAR OnlyInTable3 = EXCEPT(Table3, UNION(Table1, Table2))

RETURN
IF(Table1[Commodity number] = OnlyInTable1[Commodity number], 1,0)
 
Any help is greatly appreciated, thank you!

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure = 
VAR _table1=CALCULATETABLE(VALUES(Table1[Commodity number]),FILTER(ALL(Table1),[BM number]=MAX(Table1[BM number])))
VAR _table2=CALCULATETABLE(VALUES(Table2[Commodity number]),FILTER(ALL(Table2),[BM number]=MAX(Table2[BM number])))
VAR _table3=CALCULATETABLE(VALUES(Table3[Commodity number]),FILTER(ALL(Table3),[BM number]=MAX(Table3[BM number])))
RETURN
SWITCH(TRUE(),
SELECTEDVALUE(Table1[Commodity number]) in EXCEPT(_table1,UNION(_table2,_table3)),1,
SELECTEDVALUE(Table2[Commodity number]) in EXCEPT(_table2,UNION(_table1,_table3)),1,
SELECTEDVALUE(Table3[Commodity number]) in EXCEPT(_table3,UNION(_table1,_table2)),1,0)

    Color measure = IF([Measure],"Yellow",BLANK())

    Then add conditional formatting to each field.

    Is this the result you expected?

     

    Best Regards,

    Community Support Team _Charlotte

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

17 Replies

  • BIswajit_Das's avatar
    BIswajit_Das
    Impactful Individual
    1 year ago

    Hello Anonymous 
    Is your required result is something like the below SS

    If yes then you can create a conditional measure with DAX

    check =
    VAR _chck =
    CALCULATE(
        COUNTROWS('Table1'),
        REMOVEFILTERS('Table1'[BM number])
    )
    RETURN
    IF(_chck > 1 , 0 , 1)
    then add conditional formating on columns of the table

    Thanks &     Regards...
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi BIswajit_Das,

       

      Thank you for your time! My required result is similar to your SS, here is a sample SS of the pbix before the conditional formating:

       

      Unfortunately, when I used the measure that you suggested and added it to my table visual, it resulted in all 0 despite there being some rows that are unique to that table. I'm also not sure if I follow your logic of using REMOVEFILTERS, could you explain how you achieved the result in your SS with it?

       

      My three datasets are all duplicates and are identical if that helps 🙂 

       

      Thanks again!

      • BIswajit_Das's avatar
        BIswajit_Das
        Impactful Individual
        1 year ago

        Hello Anonymous 
        You need to use the created measure on the condition formating like the below SS

        after the measure creation select the data visual then

        expand/right click on the used columns  -> Conditional Formatting  -> Background Color ->

        Then select the format style to Rules as shown in the SS  -> Apply conditions and click on "ok" to apply the format on the data visual.
        Thanks & Regards...

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Anonymous 

     

    You can try the following methods.

    Measure = 
VAR _table1=CALCULATETABLE(VALUES(Table1[Commodity number]),FILTER(ALL(Table1),[BM number]=MAX(Table1[BM number])))
VAR _table2=CALCULATETABLE(VALUES(Table2[Commodity number]),FILTER(ALL(Table2),[BM number]=MAX(Table2[BM number])))
VAR _table3=CALCULATETABLE(VALUES(Table3[Commodity number]),FILTER(ALL(Table3),[BM number]=MAX(Table3[BM number])))
RETURN
SWITCH(TRUE(),
SELECTEDVALUE(Table1[Commodity number]) in EXCEPT(_table1,UNION(_table2,_table3)),1,
SELECTEDVALUE(Table2[Commodity number]) in EXCEPT(_table2,UNION(_table1,_table3)),1,
SELECTEDVALUE(Table3[Commodity number]) in EXCEPT(_table3,UNION(_table1,_table2)),1,0)

    Color measure = IF([Measure],"Yellow",BLANK())

    Then add conditional formatting to each field.

    Is this the result you expected?

     

    Best Regards,

    Community Support Team _Charlotte

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Anonymous,

       

      Thank you for your efforts! Unfortunately for my data, the measure could only identify some unique ingredients and missed out on a few, and some non-unique ingredients were highlighted. This occurs in all three table visuals, but it is still pretty close. I will try and find out if there is a pattern to the mistakes.

       

      Could trailing spaces in the commodity numbers affect the outcome and make a non-unique commodity number appear unique? But this does not account for when a unique commodity number is not flagged as unique (1). 

       

      Thanks again!

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi, Anonymous 

         

        Spaces in the field are what affect the results of the calculation.

         

        Best Regards,

        Community Support Team _Charlotte

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

  • Sergii24's avatar
    Sergii24
    Super User
    1 year ago

    Hi Anonymous, so you want to apply a filter, see the list of all ingridients present for that "BM number" and then highlight some rows. Is it correct? If so, what is a criteria you want to use to higlight those rows? 

    In any case, please make sure to share data sample as table so people who help you can copy-paste it instead of typing from the image 😉

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Yes that is correct! I would like to highlight the rows that only appear for BM number A, but do not appear in BM number B or C, and my plan to do so was to compare the Commodity number column between the three tables to find Commodity numbers unique to each table. 

       

      Apologies for sending the data as an image! I hope this table form works 🙂

      BM numberCommodity numberIngredientsWeight (kg)
      A12341Water9.5
      A12342Flour5.0
      A12343Eggs5.3
      A12344Milk15.0
      A12345Sugar19.3
      A12349Cocoa13.2
      A123410Cream8.5
      B56781Water19.3
      B56782Flour1.4
      B56783Eggs3.4
      B56784Milk8.4
      B56785Sugar11.0
      B56786Vanilla8.1
      B56787Almonds2.9
      B56788Pistachio10.3
      B567811Almond milk14.4
      B567812Mint5.9
      C11221Water10.2
      C11222Flour8.0
      C11223Eggs11.4
      C11224m14.5
      C11225Sugar18.3
      C11226Vanilla18.9
      C112214Matcha powder5.5
      C112215Vanilla powder16.9
      C11221680% dark chocolate14.4
      • sanalytics's avatar
        sanalytics
        Super User
        1 year ago

        Hello Anonymous 
        Below is the DAX code for your solution.

        Highlight Measure = 
VAR _NonSelectionTable =
CALCULATETABLE(
    VALUES( 'Table'[Commodity number] ),
    EXCEPT( 
        ALL('Table'[BM number] ), VALUES( 'Table'[BM number] ) )
)
VAR _ForDebug1 = 
CONCATENATEX(
    _NonSelectionTable,[Commodity number],","
)
VAR _SelectedTable =   
CALCULATETABLE(
    VALUES( 'Table'[Commodity number] ),
    VALUES('Table'[BM number] )
)
VAR _ForDebug2 = 
CONCATENATEX(
    _SelectedTable,[Commodity number],","
)
VAR _CompariosnTable = 
EXCEPT(
    _SelectedTable,_NonSelectionTable
)
VAR _ForDebug3 = 
CONCATENATEX(
    _CompariosnTable,[Commodity number],","
)
VAR _Result = 
IF(
    MAX( 'Table'[Commodity number] ) IN _CompariosnTable,"Yellow"
)
RETURN
_Result

        Below is the screenshot

        Hope it will help

         

        Regards

        sanalytics

        If it is your solution then please like and accept it as solution

  • sanalytics's avatar
    sanalytics
    Super User
    1 year ago

    Hi Anonymous 
    As Sergii24  mentioned please tell us what are the criterias of highlighting the rows and also please provide soe dummy data along with pbix file.

     

    Regards

    sanalytics

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi sanalytics and Sergii24.

      I will be able to send the sample files once I can use my personal computer as I can only share within the organisation, and I am unable to attach files. 🙂