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

     

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.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago
//This gives the number 1440, but the dax you sent me gives 1442 maybe I have the signs mixed up somewhere? thanks //count measure Performance Count greater than or equal to 0 to less than 0.01 = CALCULATE(COUNTROWS('Unitised Performance'), 'Unitised Performance'[Performance] >= 0 && 'Unitised Performance'[Performance] < 0.01 ) count = VAR tab = SUMMARIZE ( 'Unitised Performance', 'Unitised Performance'[Performance], 'Unitised Performance'[Portfolio Key] ) RETURN CALCULATE ( COUNTAX ( FILTER ( tab, [Performance] >= SELECTEDVALUE ( PerformanceBands[Low] ) && [Performance] < SELECTEDVALUE ( PerformanceBands[High] ) ), 'Unitised Performance'[Portfolio Key] ) )
  • v-yingjl's avatar
    v-yingjl
    Community Support
    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi, the second simplified dax is good.
      Since there is no relationship between the performance table and the Band table, how can I create a column char with count on y axis and band range on x axis?
      Thank you

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

        Hi Anonymous ,

        You can create a column chart like this:

         

        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.