Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Assign different value

Hi All,   Can some one please help me with the below scenario. when user selects a value from filter it should display that user name and rest all should be displayed B1, B2 .... Below is the sam...
Need Help
  • Barthel's avatar
    Barthel
    4 years ago

    Hey,

     

    Good point! It should also be possible with a disconnected table.

    Create a table with all unique users. Create a table with b variants; the same number as the number of users. Append these tables together, so you get a table like this:

    Then add two columns: one column (‘User’) in which the username is always shown and one column (‘Type’) to distinguish between username and b variant. In this case I use 'U' for username and 'B' for variant:

     

    Place the 'Value' column in a matrix and place the username column from your original table (so not the disconnected one) in a slicer. The next step is to create the right measure. Use this code for your measure:

    Measure = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( DiscUsers[Type] ) = "U",
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            KEEPFILTERS ( TREATAS ( VALUES ( DiscUsers[User] ), 'Table'[User] ) )
        ),
    SELECTEDVALUE ( DiscUsers[User] ) <> SELECTEDVALUE ( 'Table'[User] ),
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            TREATAS ( VALUES ( DiscUsers[User] ), 'Table'[User] )
        )
)

    Also in this case the B does not always start at 1. It is possible to realize this, but that would make the code more complex and that may not be ideal with real time data.

Barthel's avatar
Barthel
Solution Sage
4 years ago

Hey,

 

You can try enlarging the table with the B1, B2... data. So that the table looks like this:

This is an example code of how to create such a table in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLTJU0lEyNDBQitWB8I1AfFME3xjIt0RwTUDSZkB+LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"Sales", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "All Rows", each #"Added Index"),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Added Custom", "All Rows", {"Customer Name"}, {"Customer Name.1"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded All Rows", "Value", each if [Customer Name] = [Customer Name.1] then [Customer Name] else "B" & Text.From([Index])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Sales", "Customer Name.1", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Customer Name.1", type text}, {"Value", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Customer Name.1", "Customer Name"}})
in
    #"Renamed Columns"

Then place the 'Value' and 'Sales' of the table in a matrix visual. And put the 'Customer Name' in a slicer:


When selecting 1 user, the other users are displayed as B#. It's just not always the case that the B starts from 1.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Barthel,

     

    Thank you so much for your responce.

    This works but I have a problem that in my real time data I have huge number of records.

    so enlarging the data might not be fesiable is there any other way something like creating disconnected table and calling those values.

     

    Thank you so much in advance!