Assign different value

[praveenpasila]

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 sample data.

Customer Name	Sales
User1	100
User2	150
User3	90
User4	160

For Example when we click User1 in the filter it should display the selected value in the bar chart and rest of users should be displayed as B1, B2, B3

Expected o/p when we click User1 

Customer Name	Sales
User1	100
B1	150
B2	90
B2	160

Please suggest.

Thanks in advance

[Barthel · Accepted solution]

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.

[praveenpasila]

Hi Barthel,

The approach is working all fine but have a small issue while I am trying to filter the view using date filter leaving the selected customer all other customer sales are showing wrong I might need to add date in the dax. I tried all selected all and remove filter nothing works really helps if you can share some inputs.

Thank you

[praveenpasila]

Hi @Barthel 

Thanks for the inputs I am trying your mentioned approach but got stuck while creating user and type column could you please help me on this.

Thank you

[Barthel]

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.

[praveenpasila]

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!