Forum Discussion
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
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 = 1IDType = [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:
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
6 Replies
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 = 1IDType = [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:
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
Super User
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))
Thank you amitchandak and Anonymous for your guidance!
Evenlyn's solution was the one I was looking for.
Have a good day!
Julia
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
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
