Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Repeat every value for each id

Hi,   Is there a way to easily convert a table to list all values for each id?   This is the solution I'm looking for:   Thank you in advance!   Julia
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Please following these steps:

     

    1. Add a column whose value is 1 and a column which group ID and Type in table:

    Value = 1 
    IDType =
[ID] & [Type]

     

    2. Create a calculated table using the following formula:

    Table 2 =
VAR _tab =
    ADDCOLUMNS (
        CROSSJOIN ( ALL ( 'Table'[ID] ), ALL ( 'Table'[Type] ) ),
        "IDType", [ID] & [Type]
    )
RETURN
    ADDCOLUMNS (
        _tab,
        "Re",
            VAR _val =
                LOOKUPVALUE ( 'Table'[Value], 'Table'[IDType], [IDType] )
            RETURN
                IF ( _val = BLANK (), 0, _val )
    )

    My final output looks like this:

     

    Here is the pbix file.

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,
    Eyelyn Qin

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous,

 

Another problem concerning the vizualisation came up. Your solution is presenting the data as I want but there must be something wrong with the filtration. No matter if I choose "bananas", "apples" or "oranges", other graphs present same results. I'm wondering if this has something to do with the fact that every id has all the three categories listed. I can't figure out how to change the vizualisation to filter only the ones that have value 1 in that particular category. When filtering other graphs, the fruit vizualisation changes normally.

 

Julia