Forum Discussion

JuradoKevin14's avatar
JuradoKevin14
Frequent Visitor
2 years ago
Solved

Selected Value dax Query

I have this data but not sure on how to do the dax on this. Thank you on whoever can help me.
General Comment
Tutorial Requests
  • danextian's avatar
    danextian
    2 years ago

    A measure doesn't create rows so you won't be able to have those tabular lists. If the calculated table on your end, doesnt  give you the correct order, you can just create two extra columns to kind of select the should be column values.

    Sort2 = 
IFERROR ( VALUE ( NewTable[Sort] ), VALUE ( NewTable[Carrier] ) )

Carrier2 = 
IF (
    NewTable[Sort] = NewTable[Type],
    NewTable[Type],
    NewTable[Carrier]
)

     

JuradoKevin14's avatar
JuradoKevin14
Frequent Visitor
2 years ago

The sorting should be the same as the corresponding Carrier.

The shorting should be like this. 

 

danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

You're better off doing this in M that in DAX.  Below is the updated DAX calculated table formula but this returns the wrong order and won't change even if created as a new  table.

 

NewTable_ = 
VAR __TYPE =
    DISTINCT ( OriginalTable[Type] )
VAR __TYPE2 =
    DISTINCT (
        SELECTCOLUMNS (
            OriginalTable,
            "Type", [Type],
            "Carrier", [Type],
            "Sort", [Index]
        )
    )
VAR __CROSSJOINED =
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN (
                __TYPE,
                SELECTCOLUMNS (
                    OriginalTable,
                    "Type2", [Type],
                    "Carrier", [Carrier],
                    "Sort", [Index]
                )
            ),
            [Type] <> [Type2]
        ),
        "Type", [Type],
        "Sort", [Sort],
        "Carrier", [Carrier]
    )
VAR __LETTER_CARRIER =
    ADDCOLUMNS ( __TYPE, "Carrier", [Type] )
RETURN
    UNION ( __CROSSJOINED, __TYPE2 )

 

Here's the M code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJOLCrKTC1SMFSK1YlWckISMQKLOCOJGINFXJBETMAirkgipmARNyQRM6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Carrier = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Carrier", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Table from Previous Step" = Table.AddColumn(#"Added Index", "Previous Step", each #"Added Index"),
    #"Expanded Previous Step" = Table.ExpandTableColumn(#"Table from Previous Step", "Previous Step", {"Type", "Carrier", "Index"}, {"Previous Step.Type", "Previous Step.Carrier", "Previous Step.Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Previous Step", each [Previous Step.Type] <> [Type]),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Type", "Previous Step.Carrier", "Previous Step.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Previous Step.Index", "Index"}}),
    #"Appended Added Index to the Prev Step" = Table.Combine({#"Added Index", #"Renamed Columns"}),
    #"Removed Columns" = Table.RemoveColumns(#"Appended Added Index to the Prev Step",{"Carrier"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Carrier", each if [Previous Step.Carrier] = null then [Type] else [Previous Step.Carrier]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Previous Step.Carrier"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Type", "Carrier", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Carrier", type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Index", "Sort"}})
in
    #"Renamed Columns1"