Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
It's BITXOR but for multiple rows.
XBITXOR =
VAR __RowCount = COUNTROWS('bitor')
VAR __Length = LEN(MAX('bitor'[day_of_month_bit_position]))
VAR __String = CONCATENATEX('bitor', [day_of_month_bit_position])
VAR __Table =
ADDCOLUMNS(
GENERATESERIES(0, __RowCount * __Length - 1),
"__Bit", MID(__String, [Value]+1, 1) + 0,
"__Mod", MOD([Value], __Length)
)
VAR __Table1 =
ADDCOLUMNS(
GENERATESERIES(0, __Length - 1),
"__BITXOR",
VAR __Index = [Value]
VAR __Sum = SUMX(FILTER(__Table, [__Mod] = __Index),[__Bit])
VAR __Result = IF(__Sum = 1, 1, 0)
RETURN
__Result
)
RETURN
CONCATENATEX(__Table1, [__BITXOR],,[Value],ASC)
eyJrIjoiYjI5NTljYTYtZDlmMi00MmNjLWFiOWYtMmE0Nzc2ZGUyODQ1IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
@Greg_Deckler I think you're a bit off somewhere. For example, shouldn't customer_3, month 1 have 1 as the last digit?
Here's my attempt:
XBITOR =
VAR _BitStrings_ = DISTINCT ( 'bitor'[day_of_month_bit_position] )
VAR _Length = MAXX ( _BitStrings_, LEN ( 'bitor'[day_of_month_bit_position] ) )
VAR _BITOR_ =
ADDCOLUMNS (
GENERATESERIES ( 1, _Length ),
"@Bit",
MAXX (
_BitStrings_,
MID ( 'bitor'[day_of_month_bit_position], [Value], 1 )
)
)
VAR _ToString = CONCATENATEX ( _BITOR_, [@Bit], "", [Value] )
RETURN
_ToString
Yeah, there was an original logic error in XBITOR that I fixed on XBITOR and XBITAND but hadn't gotten around to XBITXOR yet. It's a really simple fix that involves the following two lines of code (both GENERATESERIES). I updated everything.
GENERATESERIES(0, __RowCount * __Length - 1)
and
GENERATESERIES(0, __Length - 1),
Nice.
I just realized that I posted my XBITOR in the XBITXOR thread.
For XBITXOR, you do need to use the sum logic like you have instead of a simple MAXX for XBITOR and MINX for XBITAND:
XBITXOR =
VAR _BitStrings_ =
SELECTCOLUMNS (
DISTINCT ( 'bitor'[Pos] ),
"@BitStr", 'bitor'[Pos]
)
VAR _Length = MAXX ( ALLSELECTED ( 'bitor' ), LEN ( 'bitor'[Pos] ) )
VAR _Padded_ =
ADDCOLUMNS (
_BitStrings_,
"@Padded", REPT ( "0", _Length - LEN ( [@BitStr] ) ) & [@BitStr]
)
VAR _BITOR_ =
ADDCOLUMNS (
GENERATESERIES ( 1, _Length ),
"@Bit",
VAR _Sum = SUMX ( _Padded_, INT ( MID ( [@Padded], [Value], 1 ) ) )
VAR _Result = IF ( _Sum = 1, "1", "0" )
RETURN
_Result
)
VAR _ToString = CONCATENATEX ( _BITOR_, [@Bit], "", [Value] )
RETURN
_ToString
If the bit strings are not all the same length, then you probably want to left pad with zeros like this:
XBITOR =
VAR _BitStrings_ =
SELECTCOLUMNS (
DISTINCT ( 'bitor'[Pos] ),
"@BitStr", 'bitor'[Pos]
)
VAR _Length = MAXX ( ALLSELECTED ( 'bitor' ), LEN ( 'bitor'[Pos] ) )
VAR _Padded_ =
ADDCOLUMNS (
_BitStrings_,
"@Padded", REPT ( "0", _Length - LEN ( [@BitStr] ) ) & [@BitStr]
)
VAR _BITOR_ =
ADDCOLUMNS (
GENERATESERIES ( 1, _Length ),
"@Bit", MAXX ( _Padded_, MID ( [@Padded], [Value], 1 ) )
)
VAR _ToString = CONCATENATEX ( _BITOR_, [@Bit], "", [Value] )
RETURN
_ToString
@AlexisOlson I like the idea of padding for instances of having different lengths of bits.