Forum Discussion

Krzysiek's avatar
Krzysiek
New Member
8 years ago

Grouping 1 value into 2 groups

Hi!

I have a table like 1., I know how to do 2. and I would like to do something like 3.

Could you help me? I have no clue how to do it.

For example,

I want to count rows with A - the result should be 6,

I want to count rows with B - the result should be 6,

but if I check A and B - the result shoulb be 11 (not equals 6 + 6).

4 Replies

  • Krzysiek's avatar
    Krzysiek
    New Member
    8 years ago

    Thank you! It will help a little.

     

    I have list of schools and list of big cities (9).
    Every school is related to big city.

    But if school is between 2 cities then one school is related to both (and the name looks like for example "Liverpool/Manchester".

    When I click on filter Liverpool I would like to see data connected to Liverpool (and Liverpool/Manchester) on the graph.

    If I click Manchester I would like to see data for Manchester (and Liverpool/Manchester).

    But if I check both I would like see data for Machester and Liverpool (and Liverpool/Manchester).

     

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi Krzysiek,

       

      How about expanding them like the snapshot? You can choose both of them with "CTRL".

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Best Regards,

      Dale

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Krzysiek

         

        On the lines of what v-jiascu-msft suggested, you don't need to modify your original table to split the multi valued column into 2 rows.

        Instead, you can create a new separate table which will have just 2 columns - City and Big City.

        Then make a relation between this new table and your own table. You can use the slicer with the field from this new table.

         

        Hope it helps

         

        Regards

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Krzysiek,

     

    I'm afraid we can't do like that. But we can create some measures. What's your real scenario?

    countA =
CALCULATE (
    COUNT ( Table1[Letter] ),
    SEARCH ( "A", 'Table1'[Letter], 1, 0 ) > 0
)

    countB =
CALCULATE (
    COUNT ( Table1[Letter] ),
    SEARCH ( "B", 'Table1'[Letter], 1, 0 ) > 0
)

    countAB =
CALCULATE (
    COUNT ( Table1[Letter] ),
    FILTER (
        'Table1',
        SEARCH ( "A", 'Table1'[Letter], 1, 0 ) > 0
            || SEARCH ( "B", 'Table1'[Letter], 1, 0 ) > 0
    )
)

    Grouping_1_value_into_2_groups

     

    Best Regards,

    Dale