Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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