Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

banding - count

Hello,
from a DirectQuery, I get a table named as tblPerformance which has columns like:
pKey Performance
asd 10
gfd 34
xyz 39
...

I have manually created a table for banding as follows:
tblBanding
Low High Band
0 20 less than 20
20 30 20 to less than 30
30 40 30 to less than 40
...

I would like to create a report to show:
i.e.
Band Group count
less than 20 count of pKeys where their performance is less than 20
20 to less than 30 count of pKeys where their performance is 20 to less than 30
...

Thank you

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    Try to count it directly:

    Measure = 
COUNTAX(
    FILTER(
        'tblPerformance',
        [Performance] >= SELECTEDVALUE(tblBanding[Low]) && [Performance] <= SELECTEDVALUE(tblBanding[High])
    ),
    [pKey]
)

     

    Best Regards,
    Yingjie Li

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

     

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, It looks like th eissue I am have is to do with the DirectQuery table
      because in my measure, inside the filter, I do not see in intellisense the field tblPerformance[Performance]
      Any suggestions?

      Thanks

      • v-yingjl's avatar
        v-yingjl
        Community Support
        6 years ago

        Hi Anonymous ,

        The reason that you cannot quote tblPerformance[Performance] in the filter formula in tblBanding is that there is no relationship between these tables.

        You can try this measure:

         

        count =
VAR tab =
    SUMMARIZE (
        'tblPerformance',
        'tblPerformance'[Performance],
        'tblPerformance'[pKey]
    )
RETURN
    COUNTAX (
        FILTER (
            tab,
            [Performance] >= SELECTEDVALUE ( tblBanding[Low] )
                && [Performance] < SELECTEDVALUE ( tblBanding[High] )
        ),
        [pKey]
    )

         

        tblPerformance in my sql server database using direct query in power bi desktop:

        tblBanding by entering data manually using import mode in power bi desktop:

         

        Using a table visual to show the final result:

         

        Best Regards,
        Yingjie Li

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