Formatting rows unique to only one table visual

[mfaang]

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!

[v-zhangtin-msft · Accepted solution]

Hi, @mfaang 

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.

[mfaang]

Hi @v-zhangtin-msft,

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!

[v-zhangtin-msft]

Hi, @mfaang 

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.

[mfaang]

Hi @v-zhangtin-msft,

I just trimmed all the commodity number columns but the result is still the same, are there any other formatting issues that I should check for? Thank you so much!

[v-zhangtin-msft]

Hi, @mfaang 

Can you provide example files?

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards

[mfaang]

Hi @v-zhangtin-msft ,

I am unable to attach the sample file now as it is blocked, but I hope this table with an excerpt of the real data can help!  Thank you very much

BM1	61441	Vanilla flavor	5
BM1	1492	Precipitated calcium carbonate	4
BM1	1430	Sodium citrate	3
BM1	1444	Choline chloride	2
BM2	1735	Whey protein	6
BM2	1111	Coconut oil	7
BM2	1146	sunflower oil	2
BM2	1115	MCT oil	1
BM2	61441	Vanilla flavor (artificial)	2
BM4	61441	Vanilla flavor	3
BM5	61441	Vanilla flavor	2

[mfaang]

Hi @v-zhangtin-msft ,

I deleted and remade my table visuals and your DAX works perfectly for my data now!

Thank you so much for your help!

[BIswajit_Das]

Hello @mfaang 
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...

[mfaang]

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]

Hello @mfaang 
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...

[mfaang]

Hi @BIswajit_Das,

Thank you for your explanation! I'm not too sure why it does not work for my sample or my actual data for my side, but I will look into it and let you know if I managed to get it to work.

[sanalytics]

Hi @mfaang 
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

[mfaang]

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

[Sergii24]

Hi @mfaang, 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 😉

[mfaang]

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 number	Commodity number	Ingredients	Weight (kg)
A1234	1	Water	9.5
A1234	2	Flour	5.0
A1234	3	Eggs	5.3
A1234	4	Milk	15.0
A1234	5	Sugar	19.3
A1234	9	Cocoa	13.2
A1234	10	Cream	8.5
B5678	1	Water	19.3
B5678	2	Flour	1.4
B5678	3	Eggs	3.4
B5678	4	Milk	8.4
B5678	5	Sugar	11.0
B5678	6	Vanilla	8.1
B5678	7	Almonds	2.9
B5678	8	Pistachio	10.3
B5678	11	Almond milk	14.4
B5678	12	Mint	5.9
C1122	1	Water	10.2
C1122	2	Flour	8.0
C1122	3	Eggs	11.4
C1122	4	m	14.5
C1122	5	Sugar	18.3
C1122	6	Vanilla	18.9
C1122	14	Matcha powder	5.5
C1122	15	Vanilla powder	16.9
C1122	16	80% dark chocolate	14.4

[sanalytics]

Hello @mfaang 
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

[mfaang]

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!