topic Re: Selected Value dax Query in Desktop

Selected Value dax Query

JuradoKevin14 — Thu, 11 Apr 2024 05:32:27 GMT

I have this data but not sure on how to do the dax on this.

Thank you on whoever can help me.

Re: Selected Value dax Query

danextian — Thu, 11 Apr 2024 08:05:22 GMT

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 )

Re: Selected Value dax Query

JuradoKevin14 — Thu, 11 Apr 2024 14:18:01 GMT

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?

Re: Selected Value dax Query

johnbasha33 — Thu, 11 Apr 2024 15:01:08 GMT

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 !!

Re: Selected Value dax Query

danextian — Thu, 11 Apr 2024 15:14:09 GMT

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 )

Re: Selected Value dax Query

JuradoKevin14 — Fri, 12 Apr 2024 03:08:08 GMT

The sorting should be the same as the corresponding Carrier.

The shorting should be like this.

Re: Selected Value dax Query

danextian — Fri, 12 Apr 2024 09:24:22 GMT

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"

Re: Selected Value dax Query

JuradoKevin14 — Fri, 12 Apr 2024 10:04:46 GMT

I think i am havign trouble using this concept to incorporate to my existing data model. Is it possible to use it as a measure instead?

Re: Selected Value dax Query

danextian — Fri, 12 Apr 2024 11:04:26 GMT

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

Re: Selected Value dax Query

JuradoKevin14 — Fri, 12 Apr 2024 13:31:55 GMT

The problem is i am using this table as dim to be connected to a fact table. so the relationship is many to many.. and also only A has data when it is connected becasue in the fact table the type is only shown. not the corresponding column which is the carrier. For example :