Search code examples
powerbidax

Power BI - minimal table for exclusive and full values

I have a table that looks like this

enter image description here

where column exclusive contains column names (from a, b, c) based on their column importance symbolised with '>': a > b > c

For example, if columns a, b, c all have a value (like in row 5) then a is chosen for the exclusive value because it is the most important - and when b & c have a value in the same row, b gets selected. c only gets selected when a and b both don't have a value.

From this we can plot a chart like this

enter image description here

Question Is it possible with DAX, to also create a chart like this from the same table, which contains the sum of the value column for each of a, b, c columns independent of the other columns?:

enter image description here

My current solution involves repeating data which increases the data size without increasing information content - and so I'd like to avoid it.

Like this table:

enter image description here

This table allows me to generate both charts, the latter by using the full and value columns. The former chart by filtering on is_exclusive and using exclusive with value columns.

As an advanced bonus, could I do the all the above and have variable importance? So for example, choose c > b > a or any combination.

Solution

  • You can create a Measure similar to this:

    By Column = 
  SWITCH(SELECTEDVALUE(YourTable[exclusive]),
    "a", CALCULATE(SUM(YourTable[value]), REMOVEFILTERS('YourTable'[exclusive]), YourTable[a] = 1),
    "b", CALCULATE(SUM(YourTable[value]), REMOVEFILTERS('YourTable'[exclusive]), YourTable[b] = 1),
    "c", CALCULATE(SUM(YourTable[value]), REMOVEFILTERS('YourTable'[exclusive]), YourTable[c] = 1)
  )

    Then use [exclusive] for X-axis, and the new measure for the Values.

    Ideally, you would have a separate table for the a, b, c - or categories. Example:
    Table: Dim Category

    Category
    a
    b
    c

    And then you would use this for your chart X-axis, with the measure updated to:

    By Column = 
  SWITCH(SELECTEDVALUE(`Dim Category`[Category]),
    "a", CALCULATE(SUM(YourTable[value]), YourTable[a] = 1),
    "b", CALCULATE(SUM(YourTable[value]), YourTable[b] = 1),
    "c", CALCULATE(SUM(YourTable[value]), YourTable[c] = 1)
  )

    And for completeness - you can have this measure for the first chart:

    By Exclusivity = 
  var thisCat = SELECTEDVALUE(`Dim Category`[Category])
  return CALCULATE( SUM(YourTable[value]), YourTable[exclsuive] = thisCat )