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

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