Forum Discussion

inglexjc's avatar
inglexjc
Icon for Post Patron rankPost Patron
3 years ago
Solved

Creating Range for Age Groups

I need to create a graph to show age groups, and have them color coded based on age and date they have court.   My table that has data has PPI so I can't share it.  But the table name is call...
Need Help
  • adudani's avatar
    adudani
    3 years ago

    hi inglexjc ,

     create a table as follows:

     

    table code: 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBONlTSUQJhAxDWs4QDpVidaCVDXVOgsBFUiQm6tKmuIUibMRCD1FliaDfQBmkDaTcAEwigFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Band = _t, Order = _t, Min = _t, Max = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Band", type text}, {"Order", Int64.Type}, {"Min", Int64.Type}, {"Max", type number}})
    in
    #"Changed Type"

    ______

     

    create a measure:

     

    Count Age = 
VAR __AgeBandMin = SELECTEDVALUE('Table'[Min] ) 
VAR __AgeBandMax = SELECTEDVALUE('Table'[Max] )
return
CALCULATE(
    COUNT(Table2[Age]),
    Legal Files[Age] >= __AgeBandMin,
    Legal Files[Age] <= __AgeBandMax 
)

     

     

    Appreciate a thumbs up if this helped.

     

    Please accept this as the solution if your query is resolved.

     

adudani's avatar
adudani
Icon for Memorable Member rankMemorable Member
3 years ago

hi inglexjc ,

 create a table as follows:

 

table code: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBONlTSUQJhAxDWs4QDpVidaCVDXVOgsBFUiQm6tKmuIUibMRCD1FliaDfQBmkDaTcAEwigFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Band = _t, Order = _t, Min = _t, Max = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Band", type text}, {"Order", Int64.Type}, {"Min", Int64.Type}, {"Max", type number}})
in
#"Changed Type"

______

 

create a measure:

 

Count Age = 
VAR __AgeBandMin = SELECTEDVALUE('Table'[Min] ) 
VAR __AgeBandMax = SELECTEDVALUE('Table'[Max] )
return
CALCULATE(
    COUNT(Table2[Age]),
    Legal Files[Age] >= __AgeBandMin,
    Legal Files[Age] <= __AgeBandMax 
)

 

 

Appreciate a thumbs up if this helped.

 

Please accept this as the solution if your query is resolved.

 

inglexjc's avatar
inglexjc
Icon for Post Patron rankPost Patron
3 years ago

adudani  I'm sure I'm doing something wrong.  I put in what you gave to create Table:

 

Got an error so then I tried to change things to Legal Files. Age and I'm still getting an error when creating a table.

 

  • adudani's avatar
    adudani
    Icon for Memorable Member rankMemorable Member
    3 years ago

    for the table code:

     

    please go to the power query editor.

    find blank query:

     

     

    in the advanced editor, 

     

    copy paste the table and then hit close and apply at the top left.

     

    then create the measure