Forum Discussion

dylanrollins's avatar
dylanrollins
Regular Visitor
3 years ago

Sliceable Chart Featuring Multiple Table Columns

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

KeyCompanyDepartmentQuestion1Question2Question3
1company1department1NoYesNo
2company2department2Yes Yes
3company2department3NoNoYes
4company1department2   
5company2department1YesYesYes

 

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?

Need Help
Tips and Tricks

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    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

     

    ---