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

Sergii24's avatar
Sergii24
Icon for Super User rankSuper 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
      Icon for Super User rankSuper 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hello sanalytics,

         

        Thank you so much for your help!

         

        I was able to get it to work on my sample file, but unfortunately it did not work for my actual data. I added the measure to my three table visuals, and it only highlights some unique ingredients but not all of them. The other two tables are all unhighlighted, and depending on the BM number I select, all three tables be all unhighlighted. 

        My actual data has more columns, but the columns it uses for the table visuals is the same as the sample file, would this make a difference? 

         

        I am also unable to unselect the stop interactions button for my visuals across all of these pbix files:

        The button in the top right cannot be clicked again to unselect it, and I noticed my table visuals have different interactions than the sample file. Could this be affecting it as well?


        Thanks again!