Repeat every value for each id

[Anonymous]

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

[Anonymous]

Hi @v-eqin-msft ,

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

[v-eqin-msft]

Hi @Anonymous ,

I'm a little confused with your requirement...

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @v-eqin-msft,

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

[Anonymous]

Thank you @amitchandak and @v-eqin-msft for your guidance!

Evenlyn's solution was the one I was looking for.

Have a good day!

Julia

[v-eqin-msft · Accepted solution]

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

[amitchandak]

@Anonymous , You can create using

crossjoin(all(table[id]), Table(Name))

but to get 1, 0 more stuff is needed

like -union(addcolumns(Table, "status", 1), addcolumns(except(crossjoin(all(table[id]), Table(Name)), Table), "status", 0))