Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Multiple column from a single column

I want to create table visual with multiple column from a single field

Eg: from the below table 

I want distict value from day field for each status in Multiple columns.

table visual should looks like below

 

Please help. Thanks in Advance

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous,

    You can use the following calculated table expression to group your table records into different category columns based on 'status' and 'day' fields:

    NewTable = 
FILTER (
    SELECTCOLUMNS (
        GENERATESERIES ( 1, 7, 1 ),
        "Index", [Value],
        "Full",
            LOOKUPVALUE (
                'Table'[day],
                'Table'[Status], "Full",
                'Table'[day], FORMAT ( [Value], "dddd" ),
                BLANK ()
            ),
        "Half",
            LOOKUPVALUE (
                'Table'[day],
                'Table'[Status], "Half",
                'Table'[day], FORMAT ( [Value], "dddd" ),
                BLANK ()
            ),
        "Quarter",
            LOOKUPVALUE (
                'Table'[day],
                'Table'[Status], "Quarter",
                'Table'[day], FORMAT ( [Value], "dddd" ),
                BLANK ()
            )
    ),
    [Full] & [Half] & [Quarter]
        <> BLANK ()
)

    Regards,

    Xiaoxin Sheng

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Add an index column in power query

    Power Query- Index Column: https://youtu.be/NS4esnCDqVw

     

    Create this new column in DAX

    Row = countx(filter(Table, [Status] = earlier([Status]) && [Index] <= earlier([Index]) ) , [Index])

     

    Create a Matrix, Use Row on Row , Status on Column and Max of day as value

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Its working but still have a problem, 

      It showing repeated values in each column, need to display distinct values only.

      And i couldnt find Max of Day, there are first,last,count,count(distict) options only

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous,

        You can use the following calculated table expression to group your table records into different category columns based on 'status' and 'day' fields:

        NewTable = 
FILTER (
    SELECTCOLUMNS (
        GENERATESERIES ( 1, 7, 1 ),
        "Index", [Value],
        "Full",
            LOOKUPVALUE (
                'Table'[day],
                'Table'[Status], "Full",
                'Table'[day], FORMAT ( [Value], "dddd" ),
                BLANK ()
            ),
        "Half",
            LOOKUPVALUE (
                'Table'[day],
                'Table'[Status], "Half",
                'Table'[day], FORMAT ( [Value], "dddd" ),
                BLANK ()
            ),
        "Quarter",
            LOOKUPVALUE (
                'Table'[day],
                'Table'[Status], "Quarter",
                'Table'[day], FORMAT ( [Value], "dddd" ),
                BLANK ()
            )
    ),
    [Full] & [Half] & [Quarter]
        <> BLANK ()
)

        Regards,

        Xiaoxin Sheng