Forum Discussion

SBePicas's avatar
SBePicas
Regular Visitor
2 years ago
Solved

Counting column items based on column selection

I have created three independent columns What I'd like to do is to select a column and get the number of items.  For exmple if I select Col1, I get 3 but selecting COL3 would get 6.   May be ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    HI SBePicas,

    I'd like to suggest you create a new table with three field names and use it as source of slicer.

    NewTable =
DATATABLE ( "ColName", STRING, { { "Col1" }, { "Col2" }, { "Col3" } } )

    After these steps, you can create a measure formula with switch function to check current selected value and redirect to different calculation expressions.

     

    DC based on selection =
VAR selected =
    SELECTEDVALUE ( NewTable[ColName] )
RETURN
    SWITCH (
        selected,
        "Col1", COUNTROWS ( VALUES ( Table1[Col1] ) ),
        "Col2", COUNTROWS ( VALUES ( Table1[Col2] ) ),
        "Col3", COUNTROWS ( VALUES ( Table1[Col3] ) ),
        COUNTROWS ( VALUES ( Table1[Col1] ) )
    )

    Regards,

    Xiaoxin Sheng

FreemanZ's avatar
FreemanZ
Super User
2 years ago

hi SBePicas ,

 

What do you mean by "independent"?

Are they from the same table? If they are from different tables, are the tables related?

  • SBePicas's avatar
    SBePicas
    Regular Visitor
    2 years ago

    Sorry, still learning the proper terminology.

    I have one table with 3 columns.  Each field (column) is dragged into the report editor and thus I have three visuals in the report editor.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      HI SBePicas,

      I'd like to suggest you create a new table with three field names and use it as source of slicer.

      NewTable =
DATATABLE ( "ColName", STRING, { { "Col1" }, { "Col2" }, { "Col3" } } )

      After these steps, you can create a measure formula with switch function to check current selected value and redirect to different calculation expressions.

       

      DC based on selection =
VAR selected =
    SELECTEDVALUE ( NewTable[ColName] )
RETURN
    SWITCH (
        selected,
        "Col1", COUNTROWS ( VALUES ( Table1[Col1] ) ),
        "Col2", COUNTROWS ( VALUES ( Table1[Col2] ) ),
        "Col3", COUNTROWS ( VALUES ( Table1[Col3] ) ),
        COUNTROWS ( VALUES ( Table1[Col1] ) )
    )

      Regards,

      Xiaoxin Sheng