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

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