Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
In my recent quest to create or catalog as many DAX equivalents for Excel functions, this one is for generating a unity matrix, MUNIT. It's not great, but I needed one more solution to get to 90% coverage.
MUNIT =
VAR __Dim = 10
VAR __Table =
ADDCOLUMNS(
GENERATESERIES(1,__Dim,1),
"Columns",REPT("0",[Value] - 1) & "1" & REPT("0",__Dim - [Value])
)
VAR __Column1 =
VAR __Colums = MAXX(FILTER(__Table,[Value] = 1),[Columns])
RETURN
ADDCOLUMNS(
GENERATESERIES(1,__Dim),
"1",MID(__Colums,[Value],1)
)
VAR __Column2 =
VAR __Colums = MAXX(FILTER(__Table,[Value] = 2),[Columns])
RETURN
ADDCOLUMNS(
GENERATESERIES(1,__Dim),
"2",MID(__Colums,[Value],1)
)
VAR __Column3 =
VAR __Colums = MAXX(FILTER(__Table,[Value] = 3),[Columns])
RETURN
ADDCOLUMNS(
GENERATESERIES(1,__Dim),
"3",MID(__Colums,[Value],1)
)
VAR __Column4 =
VAR __Colums = MAXX(FILTER(__Table,[Value] = 4),[Columns])
RETURN
ADDCOLUMNS(
GENERATESERIES(1,__Dim),
"4",MID(__Colums,[Value],1)
)
VAR __Column5 =
VAR __Colums = MAXX(FILTER(__Table,[Value] = 5),[Columns])
RETURN
ADDCOLUMNS(
GENERATESERIES(1,__Dim),
"5",MID(__Colums,[Value],1)
)
VAR __Column6 =
VAR __Colums = MAXX(FILTER(__Table,[Value] = 6),[Columns])
RETURN
ADDCOLUMNS(
GENERATESERIES(1,__Dim),
"6",MID(__Colums,[Value],1)
)
VAR __Column7 =
VAR __Colums = MAXX(FILTER(__Table,[Value] = 7),[Columns])
RETURN
ADDCOLUMNS(
GENERATESERIES(1,__Dim),
"7",MID(__Colums,[Value],1)
)
VAR __Column8 =
VAR __Colums = MAXX(FILTER(__Table,[Value] = 8),[Columns])
RETURN
ADDCOLUMNS(
GENERATESERIES(1,__Dim),
"8",MID(__Colums,[Value],1)
)
VAR __Column9 =
VAR __Colums = MAXX(FILTER(__Table,[Value] = 9),[Columns])
RETURN
ADDCOLUMNS(
GENERATESERIES(1,__Dim),
"9",MID(__Colums,[Value],1)
)
VAR __Column10 =
VAR __Colums = MAXX(FILTER(__Table,[Value] = 10),[Columns])
RETURN
ADDCOLUMNS(
GENERATESERIES(1,__Dim),
"10",MID(__Colums,[Value],1)
)
VAR __Final =
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(
NATURALLEFTOUTERJOIN(__Table,__Column1),
__Column2
),__Column3
),__Column4
),__Column5
),__Column6
),__Column7
),__Column8
),__Column9
),__Column10
)
RETURN
SELECTCOLUMNS(__Final,"Index",[Value],"1",[1],"2",[2],"3",[3],"4",[4],"5",[5],"6",[6],"7",[7],"8",[8],"9",[9],"10",[10])
eyJrIjoiZWNlZmM4ZmMtNWE3YS00MTFjLTk0ZWMtNDEwMTc4ZjUyOWYyIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9