Row Comparison in PowerBI - Stack Overflow

I have a table in the following format:

We have around 300 permissions with a column for each available for a profile.

I want to have a visual which highlights the differences between 2 selected rows(or Profiles).

My very optimistic view of how it would like in the end, with the user being able to select the profiles to compare:

What I tried so far was:

1. Transpose the whole thing and add to a matrix. However there are around 500 or so profiles and I couldn't make this visual type work with a field parameter so can't get to a point where the user selects the 2 profiles they would compare.
2. I created a new table with a row for each pair of profiles(a little over 170K rows yay! :) ) added 300 extra columns for each permission that show if there is a difference in the pair. Something like this:

This is the best so far, I can filter the profiles and use a field parameter with grouping so it kind of works if the user selects a profile and the specific permission they are looking for, but far from optimal. As mentioned I have around 170K rows this way, so transposing is out of the question.

I would be happiest if I could do this dynamically, but with my limited knowledge of the tool and it's capabilities what I found so far is that is not an option as table "generation" only happens during a refresh.

Happy to get any advice! Thank you!

I have a table in the following format:

We have around 300 permissions with a column for each available for a profile.

I want to have a visual which highlights the differences between 2 selected rows(or Profiles).

My very optimistic view of how it would like in the end, with the user being able to select the profiles to compare:

What I tried so far was:

1. Transpose the whole thing and add to a matrix. However there are around 500 or so profiles and I couldn't make this visual type work with a field parameter so can't get to a point where the user selects the 2 profiles they would compare.
2. I created a new table with a row for each pair of profiles(a little over 170K rows yay! :) ) added 300 extra columns for each permission that show if there is a difference in the pair. Something like this:

This is the best so far, I can filter the profiles and use a field parameter with grouping so it kind of works if the user selects a profile and the specific permission they are looking for, but far from optimal. As mentioned I have around 170K rows this way, so transposing is out of the question.

I would be happiest if I could do this dynamically, but with my limited knowledge of the tool and it's capabilities what I found so far is that is not an option as table "generation" only happens during a refresh.

Happy to get any advice! Thank you!

• powerbi
• powerbi-desktop

Share Improve this question Follow asked Jan 16 at 13:15 WehagaWehaga 2766 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Result:

It is indeed best to do this dynamically and via using Measures to do this.

First step is to reshape your table to this format via PowerQuery and Unpivot Columns.

In PowerQuery:

• Right-click on your Profile name column header, and select Unpivot Other Columns. Then in the formula bar, replace Attribute with Permission.
• Close & Reply and return to the main Power BI window.

Next, create a Measure with the following:

Profile Permission = 
  // check if there are any selections on the slicer
  var isFltr = CALCULATE(ISFILTERED(YourTable[Profile name]), ALLSELECTED(YourTable[Profile name]))

  // get the first Profile selected
  var p1 = CALCULATE(MIN(YourTable[Profile name]), ALLSELECTED(YourTable[Profile name]))

  // get the last Profile selected
  var p2 = CALCULATE(MAX(YourTable[Profile name]), ALLSELECTED(YourTable[Profile name]))

  // get the current Profile in scope
  var thisP = IF(ISINSCOPE(YourTable[Profile name]), SELECTEDVALUE('YourTable'[Profile name]))

  return
    SWITCH(TRUE(),
      // if nothing selected, display nothing
      NOT(isFltr), BLANK(),
    
      // if Profile column, show its value
      p1 = thisP || p2 = thisP, SELECTEDVALUE('YourTable'[Value]),

      // for the Total aka Difference
      NOT(ISINSCOPE(YourTable[Profile name])) && p1 <> p2,
        var p1v = CALCULATE(FIRSTNONBLANK(YourTable[Value], 1), YourTable[Profile name] = p1)
        var p2v = CALCULATE(FIRSTNONBLANK(YourTable[Value], 1), YourTable[Profile name] = p2)
        return IF(p1v <> p2v, 1, 0)
    )

With this new measure selected, go to Measure tools in the ribbon, change the Format to Dynamic and enter the following:

"TRUE;;FALSE;"

Now create your Matrix visual with:

Rows: [Permission]
Columns: [Profile name]
Values: [Profile Permission]

Then in the Matrix visual properties, switch off Row subtotals, and in Column subtotals, expand it and rename the Subtotal label to Difference.

Next, add a Slicer visual, and use [Profile name].

Lastly, for the Conditional Formatting. Right-click on Profile Permission in the Matrix Values well, and select Conditional formatting > Background color, set it as follows:

Optional step:
You may want to always show the Permissions when no selection is made in the slicer. If so, right-click on Permission in the Matrix Rows well, and select Show items with no data.

The primary rule of thumb is PBI does NOT like 2-d dataset; unpivot it.

The measure itself is not complex,

Check = 
FORMAT (
    IF (
        HASONEVALUE ( DATA[Profile] ),
        MAXA ( DATA[Value] ),
        DISTINCTCOUNT ( DATA[Value] ) - 1
    ),
    "\Y;;\N"
)

The tricky part is how to make use of "Column subtotals" of Matrix viz to display the result,