Forum Discussion

gth's avatar
gth
New Member
4 years ago
Solved

Counting text in column

Hi everyone

I've created the following measure to count the number of rows when specific columns are filtered, but it's not working. All columns are formatted as text (as I need to retain leading zeros) and I have tried COUNTROWS(), COUNTA() and COUNTAX(), but no joy. Any ideas greatly appreciated? ðŸ˜Š

 

5. in high-skilled employment 2017/18 =
CALCULATE (
COUNT (all_core_data[HUSID]),
( all_core_data[XWRKHSKILL] IN { "01" } ),
all_core_data[XACTIVITY] in {"01","02","03","04","05"},
all_core_data[year] = "2017/18"
)
Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    Hi gth ,

     

    It is really helpful to answer if you could share sample data in text format with expected output. In the meantime you can try any of these code:-

    5. in high-skilled employment 2017/1 =
COUNTROWS (
    FILTER (
        all_core_data,
        all_core_data[XWRKHSKILL]
            IN { "01" }
            && all_core_data[XACTIVITY]
            IN { "01", "02", "03", "04", "05" }
            && all_core_data[year] = "2017/18"
    )
)

    or 

    5. in high-skilled employment 2017/1 =
CALCULATE (
  COUNT (all_core_data[HUSID]),
    FILTER (
        all_core_data,
        all_core_data[XWRKHSKILL]
            IN { "01" }
            && all_core_data[XACTIVITY]
            IN { "01", "02", "03", "04", "05" }
            && all_core_data[year] = "2017/18"
    )
)

     

    Thanks,

    Samarth

6 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    4 years ago

    Hey gth ,

     

    th approach doesn't look wrong to me. But it's hard to say why it doesn't work without more information.

    Can you share the file or create a sample file? That would make it easier to understand why it's not working in your case.

     

    Best regards
    Denis 

    • gth's avatar
      gth
      New Member
      4 years ago

      Hey selimovd 

      Thanks so much for your reply. I'll try and provide some sample data - apologies for not doing that initially...am very new to all this.

      Thanks again

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi gth ,

     

    It is really helpful to answer if you could share sample data in text format with expected output. In the meantime you can try any of these code:-

    5. in high-skilled employment 2017/1 =
COUNTROWS (
    FILTER (
        all_core_data,
        all_core_data[XWRKHSKILL]
            IN { "01" }
            && all_core_data[XACTIVITY]
            IN { "01", "02", "03", "04", "05" }
            && all_core_data[year] = "2017/18"
    )
)

    or 

    5. in high-skilled employment 2017/1 =
CALCULATE (
  COUNT (all_core_data[HUSID]),
    FILTER (
        all_core_data,
        all_core_data[XWRKHSKILL]
            IN { "01" }
            && all_core_data[XACTIVITY]
            IN { "01", "02", "03", "04", "05" }
            && all_core_data[year] = "2017/18"
    )
)

     

    Thanks,

    Samarth

    • gth's avatar
      gth
      New Member
      4 years ago

      Hi Samarth_18 

      Thanks so much for your reply and for your suggestions. I'll try and provide some sample data - apologies for not doing that initially...am very new to all this.

      Thanks again

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, gth 

     

    May I ask if your problem has been solved? Is the above post helpful to you?

    If  it does, could you please mark the post which help as Answered? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

     

    It makes it easier to give you a solution.

    1. Sample (dummy dataset) data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • gth's avatar
      gth
      New Member
      4 years ago

      Hi. Apologies for not doing that sooner...and thank you too for the useful advice - I'll remember that for next time. Thanks again