Dax measure. Ignore blanks/nulls in comparing columns

[DeeA]

Hello,
Please i want to write a calculated column (Moved_Static in below) to indicate movement across cities per misisdn. and also number of occurences of the movement.

This is what i drafted 

Moved_Static =

IF(
NOT(ISBLANK(locations_v2[1/1/2023]))=NOT(ISBLANK(locations_v2[2/1/2023]))&&
NOT(ISBLANK(locations_v2[2/1/2023]))=NOT(ISBLANK(locations_v2[3/1/2023]))&&
NOT(ISBLANK(locations_v2[3/1/2023]))=NOT(ISBLANK(locations_v2[4/1/2023]))&&
NOT(ISBLANK(locations_v2[4/1/2023]))=NOT(ISBLANK(locations_v2[5/1/2023]))&&
NOT(ISBLANK(locations_v2[5/1/2023]))=NOT(ISBLANK(locations_v2[6/1/2023]))&&
NOT(ISBLANK(locations_v2[6/1/2023]))=NOT(ISBLANK(locations_v2[7/1/2023]),
"Static",
"Moved")

and this is the result i get in the last column.

msisdn_key	1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	Moved_Static
3333333	City1	City1	City1	City1	City1	City1		Moved
5555555	City3	City3	City3	City3		City3		Moved
7777777	City4	City4	City5	City5		City5		Moved
8888888	City6	City6		City6	City6	City6		Moved
9999999		City7		City7	City8	City9		Moved

but i want t ignore the nulls and get this below;

msisdn_key	1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	Moved_Static
3333333	City1	City1	City1	City1	City1	City1		Static
5555555	City3	City3	City3	City3		City3		Static
7777777	City4	City4	City5	City5		City5		Moved
8888888	City6	City6		City6	City6	City6		Moved
9999999		City7		City7	City8	City9		Moved

Please help

[Ashish_Mathur]

Hi,

You have got a calculated column formula answer which is great.  In my opinion, you should first use the "Unpivot Other Columns" feature to transform your dataset into a 3 column one.  This will make it easier for your to answer the question that you have and do truck loads of other analysis with a lot of ease.  Just in case you do not want to use the "Unpivot Other columns" feature, you may use this M code to generate another column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMoYAJR0l58ySSkMSaKVYnWglUwiAChvjoZGZIJ3mEAAVNkGjTZFoZCZIpwUEQIXNkGgsQnApkE5LCECoNEdlgmiYuWBVsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [msisdn_key = _t, #"01-01-2023" = _t, #"02-01-2023" = _t, #"03-01-2023" = _t, #"04-01-2023" = _t, #"05-01-2023" = _t, #"06-01-2023" = _t, #"07-01-2023" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if List.Count(List.Distinct(List.Select(List.Skip(Record.ToList(_),1), each _<>"")))=1 then "Static" else "Moved")
in
    #"Added Custom"

Hope this helps.

[DeeA]

@Ashish_Mathur thanks for your feedback.

the dataset was initially a 3 column one and i pivoted to get it in the format i shared because it looked easier to answer the question i had. i am however open to trying a solution with the 3 column dataset to answer my question of showing the movement across the cities over the past months.

thannks

[Ashish_Mathur]

Hi,

Once you have a 3 column table, you may just drag the msisdn_key to the visual and write this measure

Measure = if(distinctcount(Data[Date])>1,"Moved","Statuc")

Hope this helps.

[parry2k]

@DeeA blank value and an empty string are two different things, my formula is checking a blank (aka null) value, not an empty string. Maybe tweak it like this:

Moved - Static = 
VAR __MoveCount =
COUNTROWS ( 
    FILTER ( 
        DISTINCT ( 
            { 
                'Move'[1/1/2023],
                'Move'[2/1/2023],
                'Move'[3/1/2023],
                'Move'[4/1/2023],
                'Move'[5/1/2023],
                'Move'[6/1/2023],
                'Move'[7/1/2023]
            }
        ),
        COALESCE ( [Value], "" ) <> ""
    )
)
RETURN
IF ( __MoveCount > 1, "Moved " & FORMAT ( __MoveCount, "###" ) & " times", "Static" )

[parry2k · Accepted solution]

@DeeA add new calculated column using the following expression

Moved - Static = 
VAR __MoveCount =
COUNTROWS ( 
    FILTER ( 
        DISTINCT ( 
            { 
                'Move'[1/1/2023],
                'Move'[2/1/2023],
                'Move'[3/1/2023],
                'Move'[4/1/2023],
                'Move'[5/1/2023],
                'Move'[6/1/2023],
                'Move'[7/1/2023]
            }
        ),
        [Value] <> BLANK ( ) 
    )
)
RETURN
IF ( __MoveCount > 1, "Moved " & FORMAT ( __MoveCount, "###" ) & " times", "Static" )

[DeeA]

Thanks for this!

so for the areas where there were blanks it indicates inactivity. how will incorporate that into the measure. so that for something like below i can indicate the months of inactivity

msisdn_key	1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	Moved_Static
3333333	City1	City1	City1	City1	City1	City1		Moved
5555555	City3	City3	City3	City3		City3		Moved
7777777	City4	City4	City5	City5		City5		Moved
8888888	City6	City6		City6	City6	City6		Moved
9999999		City7		City7	City8	City9		Moved