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]
)

     

danextian's avatar
danextian
Super User
2 years ago

Hi JuradoKevin14 ,

 

SELECTEDVALUE won't work. You need to make the list physically appear in the model for every value in Type.  Assuming that the Carrier value is to be replaced by Type value, use this sample DAX calculated table.

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

 

  • JuradoKevin14's avatar
    JuradoKevin14
    Frequent Visitor
    2 years ago

    Hi sir thank you got it. but i am fixing the sort so that it would be in ascending order. but I cant seem to put it in the filter page? 

     

     

     

    • johnbasha33's avatar
      johnbasha33
      Super User
      2 years ago

      JuradoKevin14 

      select the Type column and go to columntools and click on sort option, here you need to select Sort column. we basically specifying to use Sort column to sort Type column with this approach. 

      Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

    • danextian's avatar
      danextian
      Super User
      2 years ago

      Modidfy the calculated table formula to below to add a sort column. All Carrier values will have a blank sort while the rest will have 1. 

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

       

      • 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.