Dynamic Histogram

[Anonymous]

Hello everyone,

I need to create a histogram graph using the followwing table.

The y axis must show the distinct count of codes.

The x axis must show the number of months that a code repeats, also i need the x-axis to be dynamic. For example, if i filter month = 1  and month = 3 that the graph only counts the selected codes.

Month	Code
1	3712
3	9476
2	11333
3	18679
2	18679
1	20709
2	20709
1	29570
2	29570
3	29667
2	29667
1	34088
3	36865
1	36865
2	36865
3	37635
1	41631
3	44680
1	45082
2	45082
1	45948
3	47994
2	47994
3	48762
2	48762
3	49813
2	49813
3	50299
1	50299
2	50299
3	50300
1	50300
2	50300
1	51191
2	51191
3	51191
3	51281
1	51281
3	53438
3	53920
1	54539
2	54539
3	54703
1	54703
2	54703
2	54877
3	54877
1	54877
1	58681
3	58681
3	58997
1	58997
2	58997
2	63316
3	63316
3	64180
1	67375
3	68015
2	76118
2	77187
1	80149
3	84423
3	91049
3	98200
3	100974
2	100974
3	101165
2	101165
2	101607
3	101607
3	103539
1	103539
2	103539
3	104469
2	104469
1	104534
3	109243
1	110777
2	110777
3	117756
1	117756
2	117756
1	118641
2	118641
2	123189
1	125201
2	125561
3	200397
2	200397
3	200447
1	200447
2	200447
2	310451
1	311772
1	311779
1	312960
2	312960
1	315453
2	315493
3	315493
1	315493
1	316130
1	316222
3	317674
2	317674
3	318260
1	320431
1	321047
3	321047
1	321318
3	321700
2	321700

The final solution should be as follows:

The example filtering with month = 1  and month = 3:

Best Regrads,

Mario Ortiz Lazo

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculated table uas the x-axis:

X-axis = 
GENERATESERIES (
    1,
    MAXX (
        SUMMARIZE ( ALL ( 'Table' ), [Code], "Count", COUNTROWS ( 'Table' ) ),
        [Count]
    ),
    1
)

Then we can create a measure to count the repeat code dymanicaly:

Total = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            GROUPBY ( 'Table', 'Table'[Code] ),
            "RepetCount", VAR c = [Code] RETURN CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Code] = c )
        ),
        [RepetCount] IN FILTERS ( 'X-axis'[Value] )
    )
)

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculated table uas the x-axis:

X-axis = 
GENERATESERIES (
    1,
    MAXX (
        SUMMARIZE ( ALL ( 'Table' ), [Code], "Count", COUNTROWS ( 'Table' ) ),
        [Count]
    ),
    1
)

Then we can create a measure to count the repeat code dymanicaly:

Total = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            GROUPBY ( 'Table', 'Table'[Code] ),
            "RepetCount", VAR c = [Code] RETURN CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Code] = c )
        ),
        [RepetCount] IN FILTERS ( 'X-axis'[Value] )
    )
)

Best regards,

[Anonymous]

Thank you for the response.

I have an error message when i create the calculated table (x-axis) with the maxx function.

It seems that the calculated table can´t recognized the [count] column

MAXX ( SUMMARIZE ( ALL ( 'Table' ); [code]; "Count"; COUNTROWS ( 'Table' ) ); [Count])

Error message: The expression specified in the query is not a valid table expression.

[Anonymous · Accepted solution]

I could solve it!!!!

Thank you very much.

Best regrads,

Mario Ortiz Lazo