Sliceable Chart Featuring Multiple Table Columns

[dylanrollins]

Hi,

I have a data table based on responses to an MS Form, with several yes/no questions being split out over separate columns. I.e.:

Key	Company	Department	Question1	Question2	Question3
1	company1	department1	No	Yes	No
2	company2	department2	Yes		Yes
3	company2	department3	No	No	Yes
4	company1	department2
5	company2	department1	Yes	Yes	Yes

I need to have the % of "Yes" answers for each question combined into a single bar chart (descending based on which question has the highest "Yes" %), but also have the chart be sliceable based on both Company and Department.

I've got the following chart:

This was achieved using a calculated table, created with the following DAX:

calculated_table =
{
    ( "Question1", CALCULATE (
        COUNTROWS ( table1 ),
        FILTER ( 'table1', 'table1'[Question1] = "Yes" )
    )
        / CALCULATE (
            COUNTROWS ( table1 ),
            FILTER ( 'table1', 'table1'[Question1] <> "" )
        ) ),
    ( "Question2", CALCULATE (
        COUNTROWS ( table1 ),
        FILTER ( 'table1', 'table1'[Question2] = "Yes" )
    )
        / CALCULATE (
            COUNTROWS ( table1 ),
            FILTER ( 'table1', 'table1'[Question2] <> "" )
        ) ),
    ( "Question3", CALCULATE (
        COUNTROWS ( table1 ),
        FILTER ( 'table1', 'table1'[Question3] = "Yes" )
    )
        / CALCULATE (
            COUNTROWS ( table1 ),
            FILTER ( 'table1', 'table1'[Question3] <> "" )
        ) )
}

Which gave this result (with 'Column' being dropped in as the Y axis field):

However, as this is a calculated table, it can't be affected by slicers.

The only other thing I can think of is to create separate measures for each column, then add them all into one chart. This will respond to selected slicer values, but just kind of looks terrible (no Y value labels, and moving the legend down the left barely works):

Are there any workarounds for making calculated tables filterable by slicers, or any other completely different ideas?

[DataInsights]

@dylanrollins,

A simpler solution is to unpivot the Question columns in Power Query. Here's the M code. I added the step ReplaceEmptyWithNull to replace empty strings with null (your model may not require this step, depending on your data).

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WMlTSUUrOzy1IzKsEMVNSCxKLSnJT80pAPL98IBGZWgxhxupEKxkhlBuhKDeCq4QyQKqNcak2hhkOtwGk3ASXW4wgxoIQSJ0pLmMN4Y5AkLGxAA==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Key = _t,
        Company = _t,
        Department = _t,
        Question1 = _t,
        Question2 = _t,
        Question3 = _t
      ]
  ),
  ChangeType = Table.TransformColumnTypes(
    Source,
    {
      {"Key", Int64.Type},
      {"Company", type text},
      {"Department", type text},
      {"Question1", type text},
      {"Question2", type text},
      {"Question3", type text}
    }
  ),
  UnpivotColumns = Table.UnpivotOtherColumns(
    ChangeType,
    {"Key", "Company", "Department"},
    "Attribute",
    "Value"
  ),
  RenameColumn = Table.RenameColumns(UnpivotColumns, {{"Attribute", "Question"}}),
  ReplaceEmptyWithNull = Table.ReplaceValue(
    RenameColumn,
    "",
    null,
    Replacer.ReplaceValue,
    {"Value"}
  )
in
  ReplaceEmptyWithNull

Create measure:

Yes Answers = 
VAR vNumerator =
    CALCULATE ( COUNT ( Table1[Value] ), Table1[Value] = "Yes" )
VAR vDenominator =
    COUNT ( Table1[Value] )
VAR vResult =
    DIVIDE ( vNumerator, vDenominator )
RETURN
    vResult

---