I need help filling a matrix with a Site ID based on a priority number

[JedShields]

Hi all,

I've set up a matrix with row Headers 1-20, 21-40 and column headers 1-20.  I then populate each cell with a site number.  So the top row starts with Site 1 and finishes with Site 20, the next row starts Site 21 and finishes with Site 40.  I added the hard typed Site Numbers ("Site 1", "Site 2" etc.)  just to check that the matrix works.  I need to change the hard typed reference to an actual Site ID based on a reference number.  For example if Site ABC123 had a Ref Number of 1, then it would go in the top left cell.  If Site XYZ789 had Ref Number 40 then it would populate the bottom right cell.  How do I change the code to do a lookup of the site ID based on the Ref ID?  With the Ref ID being a number between 1 and 40.

The Site ID and Ref ID are located in a table that I have a direct connection to.

Here is my DAX code so far:

Matrix Measure = 
// creates and populates a grid table with Site Groups (of 20) and specific site number as columns

SWITCH (
    TRUE (),

    --Lock 1 - 20 Row
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 1, "Site 1",
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 2, "Site 2",
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 3, "Site 3",
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 4, "Site 4",    
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 5, "Site 5",
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 6, "Site 6",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 7, "Site 7",
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 8, "Site 8",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 9, "Site 9",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 10, "Site 10",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 11, "Site 11",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 12, "Site 12",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 13, "Site 13",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 14, "Site 14",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 15, "Site 15",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 16, "Site 16",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 17, "Site 17",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 18, "Site 18",        
      SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 19, "Site 19",      
     SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "1-20"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 20, "Site 20",  
            



   --Lock 21-40 Row
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 1, "Site 21",
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 2, "Site 22",
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 3, "Site 23",
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 4, "Site 24",    
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 5, "Site 25",
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 6, "Site 26",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 7, "Site 27",
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 8, "Site 28",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 9, "Site 29",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 10, "Site 30",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 11, "Site 31",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 12, "Site 32",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 13, "Site 33",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 14, "Site 34",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 15, "Site 35",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 16, "Site 36",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 17, "Site 37",        
    SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 18, "Site 38",        
      SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 19, "Site 39",      
     SELECTEDVALUE ( TableRow01[TableRow01Names] ) = "21-40"
        && SELECTEDVALUE ( TableCol01[TableCol01Names] ) = 20, "Site 40"  


        )

[v-hashadapu]

Hi @JedShields , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @JedShields , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @JedShields , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.

[JedShields]

I've got something working, although not convinced it's particulalry efficient!  I've replaced the "Site x" part with the following:

 LOOKUPVALUE('randata report_dart2_main'[mbnl_cell_id],'randata report_dart2_main'[LOCK],1),

[lbendlin · Accepted solution]

Both your approaches can be improved. Read about variables, and consider using the data model or TREATAS in lieu of LOOKUPVALUE.

Read about Modulo, and integer divisions.

// Query1
let
    Source = {1..40},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Site"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Name", each "Site" & Text.From([Site]),type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Row", each Number.Mod([Site]-1,20),Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Column", each Number.RoundDown(([Site]-1)/20),Int64.Type),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Site", Int64.Type}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.