Count Users and Company (Between this value)

[gauravnarchal]

Need Help!

I need to create 2 measures and would need your help.

1) Count companies that are having total user count between 0-2, 2-4 and 4-10.

2) Count companies that are having total user count between 1-3, 3-6 and 6-10 and are Active.

[Anonymous · Accepted solution]

Hi @gauravnarchal ,

I also considered time filtering. If you have a Date column in your table, you can achieve dynamic results through slicer selection. It doesn't matter if there is no Date column, it does not affect the result.

My sample data is this.

Name	Company	Active	Date
User1	ABC	FALSE	1/1/2020
User2	ABC	FALSE	1/2/2020
User3	ABC	TRUE	1/3/2020
User4	ABC	TRUE	1/2/2020
User5	ABC	FALSE	1/3/2020
User1	ABC123	FALSE	1/4/2020
User2	ABC123	FALSE	1/1/2020
User3	ABC123	TRUE	1/2/2020
User4	ABC123	TRUE	1/1/2020
User5	ABC123	FALSE	1/2/2020
User6	ABC123	FALSE	1/3/2020
User7	ABC123	FALSE	1/4/2020
User8	ABC123	TRUE	1/1/2020
User1	TEST1	TRUE	1/2/2020
User2	TEST1	FALSE	1/2/2020
User3	TEST1	FALSE	1/2/2020
User4	TEST1	FALSE	1/1/2020
User5	TEST1	TRUE	1/2/2020
User6	TEST1	TRUE	1/2/2020
User7	TEST1	FALSE	1/2/2020
User8	TEST1	FALSE	1/2/2020
User9	TEST1	FALSE	1/1/2020
User10	TEST1	TRUE	1/2/2020
User1	TEST1	TRUE	1/1/2019
User2	TEST1	FALSE	1/2/2019
User3	TEST1	FALSE	1/3/2019
User4	TEST1	FALSE	1/1/2019
User5	TEST1	TRUE	1/4/2019
User6	TEST1	TRUE	1/1/2019
User7	TEST1	FALSE	1/2/2019
User8	TEST1	FALSE	1/3/2019
User9	TEST1	FALSE	1/1/2019
User10	TEST1	TRUE	1/4/2019

1.You can try to create a new table by entering data as shown below.

2.Then these are measures that count companies that are having total user count between 0-2, 2-4 and 4-10.

Measure = 
CALCULATE ( COUNTROWS ( 'Table' ), FILTER(ALLSELECTED('Table'),'Table'[Company]=MAX('Table'[Company])))

Count =
VAR _02 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Company] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Measure] >= 0
                && 'Table'[Measure] < 2
        )
    )
VAR _24 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Company] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Measure] >= 2
                && 'Table'[Measure] < 4
        )
    )
VAR _410 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Company] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Measure] >= 4
                && 'Table'[Measure] <= 10
        )
    )
RETURN
    SWITCH (
        MAX ( 'Table (2)'[total user count] ),
        "0-2", IF ( ISBLANK ( _02 ), 0, _02 ),
        "2-4", IF ( ISBLANK ( _24 ), 0, _24 ),
        "4-10", IF ( ISBLANK ( _410 ), 0, _410 )
)

3.These are measures that count companies that are having total user count between 1-3, 3-6 and 6-10 and are Active.

Measure 2 = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( ALLSELECTED(  'Table'),'Table'[Company]=MAX('Table'[Company])&& [Active] = "True" )
)

Count 2 = 
VAR _13 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Company] ),
        FILTER ( ALLSELECTED( 'Table' ), 'Table'[Measure 2] >= 1 && 'Table'[Measure 2] < 3 )
    )
VAR _36 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Company] ),
        FILTER ( ALLSELECTED( 'Table' ), 'Table'[Measure 2] >= 3 && 'Table'[Measure 2] < 6 )
    )
VAR _610 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Company] ),
        FILTER ( ALLSELECTED( 'Table' ), 'Table'[Measure 2] >= 6 && 'Table'[Measure 2] <= 10 )
    )
RETURN
    SWITCH (
        MAX ( 'Table (2)'[total user count and acitve] ),
        "1-3", IF ( ISBLANK ( _13 ), 0, _13 ),
        "3-6", IF ( ISBLANK ( _36 ), 0, _36 ),
        "6-10", IF ( ISBLANK ( _610 ), 0, _610 )
    )

You can check more details from here.

Best Regards,
Stephen Tao

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

[amitchandak]

@gauravnarchal , you need to have a measure. Also, you need to have a bucket table. Using the count measure and this bucket table you have to create another measure which makes sure companies fall into the bucket

refer

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

https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bi...
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-i...

[FarhanAhmed]

Try check following

https://www.daxpatterns.com/dynamic-segmentation/

https://radacad.com/customers-grouped-by-the-count-of-their-orders-dynamic-segmentation-in-power-bi-...