banding - count

[Anonymous]

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

[amitchandak]

@Anonymous , refer if this can help

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Anonymous]

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]

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]

hi, what does the word tab do in the filter? I thought it should be a table instead?

Thanks

[Anonymous]

//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 · Accepted solution]

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]

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]

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.