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 ,

 

Let me try again. The filtration is working fine with a normal bar chart but I need to present the results in a 100 stacked bar chart. I'd like to show statistics over people with "Oranges", but somehow the visualization is not capable to make any difference between 1 and 0. As every id is having every fruit listed, the visualization is showing same result no matter what fruit has been selected. 

 

I'd like "Oranges" to be 3, not the total of 5.

The filtration works as wanted when filtering yes = 1 but this way the visualization is not showing the relative percentage which is very critical in this report. 

The data is structured like this:

 

You can download the pbix here: 

https://drive.google.com/drive/folders/1LNfBv3rOuk2giddLlsYhtPcCF75k9kND?usp=sharing