topic Re: Using multiple filters and a measure as a source and destinations for a Sankey Visual in Desktop
https://community.powerbi.com/t5/Desktop/Using-a-Multiple-filters-and-a-Measure-as-the-Source-and/m-p/1559544#M636400
<P>Thanks very much Allison. </P><P>Each person has 6 scores for each reporting period and there are 6 reporting periods a year and there are 100K entrys per report. Pivoting the table is not an option for me so I was trying to find a way to use a measure to sort this out. We also use AnalysisServices. </P><P>You asked a great question, what am I trying to show? I'm trying to find people that have moved from one level to another over time. It appears that most remain at the same level, but some move up, and others down. I'd really like to have the pattern continue over several reports or years, but one step at a time. :-).</P><P> </P><P>Thanks for attempt and much appreciated. I was able to generate my 'ideal' image by creating a small data set and doing what you did. Very intensive to do for each reporting period. Any more thoughts or ideas? I've watched many videos and read many forums and tried many measure codes, but I cannot get it to work for me. </P>Fri, 18 Dec 2020 13:12:43 GMTSO2020-12-18T13:12:43ZUsing a Multiple filters and a Measure as the Source and Destinations for a Sankey Visual
https://community.powerbi.com/t5/Desktop/Using-a-Multiple-filters-and-a-Measure-as-the-Source-and/m-p/1558177#M636099
<P>I have data that looks simplified looks something like this </P><P> </P><TABLE border="1"><TBODY><TR><TD>PersonID</TD><TD> </TD><TD>Year</TD><TD> </TD><TD>Report</TD><TD> </TD><TD>Score</TD><TD> </TD><TD>Level</TD></TR><TR><TD>123</TD><TD> </TD><TD>20192020</TD><TD> </TD><TD>1M</TD><TD> </TD><TD>65</TD><TD> </TD><TD>L2</TD></TR><TR><TD>134</TD><TD> </TD><TD>20192020</TD><TD> </TD><TD>1M</TD><TD> </TD><TD>55</TD><TD> </TD><TD>L1</TD></TR><TR><TD>123</TD><TD> </TD><TD>20192020</TD><TD> </TD><TD>1M</TD><TD> </TD><TD>67</TD><TD> </TD><TD>L2</TD></TR><TR><TD>145</TD><TD> </TD><TD>20192020</TD><TD> </TD><TD>1M</TD><TD> </TD><TD>95</TD><TD> </TD><TD>L4</TD></TR><TR><TD>123</TD><TD> </TD><TD>20202021</TD><TD> </TD><TD>1M</TD><TD> </TD><TD>75</TD><TD> </TD><TD>L3</TD></TR><TR><TD>134</TD><TD> </TD><TD>20202021</TD><TD> </TD><TD>1M</TD><TD> </TD><TD>55</TD><TD> </TD><TD>L1</TD></TR><TR><TD>134</TD><TD> </TD><TD>20202021</TD><TD> </TD><TD>1M</TD><TD> </TD><TD>45</TD><TD> </TD><TD>R</TD></TR><TR><TD>145</TD><TD> </TD><TD>20202021</TD><TD> </TD><TD>1M</TD><TD> </TD><TD>78</TD><TD> </TD><TD>L3</TD></TR></TBODY></TABLE><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Screenshot.png" style="width: 870px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/419252iE6D22FDC0D875BB1/image-size/large?v=v2&px=999" role="button" title="Screenshot.png" alt="Sankey Mockup" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Sankey Mockup</span></span></P><P>I'd like to produce a Sankey Chart that looks something like above. </P><P> </P><P>The best I can do is something like this: </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Screenshot2.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/419258iBC113BEE896233BB/image-size/medium?v=v2&px=400" role="button" title="Screenshot2.png" alt="Screenshot2.png" /></span></P><P> </P><P>I've tried every combination of source, destination, label, etc. without success. </P><P> </P><P>I then tried to create to measures so that I could filter out the year and use one measure for the Destination and another for the Destination Labels.</P><P> </P><DIV><DIV><SPAN>#Average 20192020 = </SPAN></DIV><DIV><SPAN>VAR __BASELINE_VALUE =</SPAN></DIV><DIV><SPAN> CALCULATE(</SPAN></DIV><DIV><SPAN> AVERAGE('Table '[Score]),</SPAN></DIV><DIV><SPAN> 'Table'[Year]</SPAN></DIV><DIV><SPAN> IN { "20192020" }</SPAN></DIV><DIV><SPAN> )</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN> __BASELINE_VALUE</SPAN></DIV></DIV><P> </P><P>and </P><P> </P><DIV><SPAN>#Average 20202021 = </SPAN></DIV><DIV><SPAN>VAR __BASELINE_VALUE =</SPAN></DIV><DIV><SPAN> CALCULATE(</SPAN></DIV><DIV><SPAN> AVERAGE('Table '[Score]),</SPAN></DIV><DIV><SPAN> 'Table'[Year]</SPAN></DIV><DIV><SPAN> IN { "20202021" }</SPAN></DIV><DIV><SPAN> )</SPAN></DIV><DIV><SPAN>RETURN</SPAN></DIV><DIV><SPAN> __BASELINE_VALUE</SPAN></DIV><P> </P><P>I can pull these measures into a table and use them, but they will not drag into any of the Sankey fields.</P><P>I'm obviously missing something. I would like to do this in DAX without going to data tables. </P><P> </P><P>Thoughts on how to proceed? Curious ?</P><P> </P><P>SO </P><P> </P>Fri, 18 Dec 2020 05:30:45 GMThttps://community.powerbi.com/t5/Desktop/Using-a-Multiple-filters-and-a-Measure-as-the-Source-and/m-p/1558177#M636099SO2020-12-18T05:30:45ZRe: Using a Multiple filters and a Measure as the Source and Destinations for a Sankey Visual
https://community.powerbi.com/t5/Desktop/Using-a-Multiple-filters-and-a-Measure-as-the-Source-and/m-p/1558522#M636178
<P><LI-USER uid="193381"></LI-USER> What is the context of this data? What is it you want to communicate? </P>
<P> </P>
<P>I don't fully understand, but have tried pivoting your data so that you have one column for source and one column for destination, I'm just not sure what to use for the values in these columns (I used Level and set to don't aggregate), but not every person ID has data for both years... </P>
<P> </P>
<P>See file attached below signature.</P>Fri, 18 Dec 2020 08:01:43 GMThttps://community.powerbi.com/t5/Desktop/Using-a-Multiple-filters-and-a-Measure-as-the-Source-and/m-p/1558522#M636178AllisonKennedy2020-12-18T08:01:43ZRe: Using multiple filters and a measure as a source and destinations for a Sankey Visual
https://community.powerbi.com/t5/Desktop/Using-a-Multiple-filters-and-a-Measure-as-the-Source-and/m-p/1559544#M636400
<P>Thanks very much Allison. </P><P>Each person has 6 scores for each reporting period and there are 6 reporting periods a year and there are 100K entrys per report. Pivoting the table is not an option for me so I was trying to find a way to use a measure to sort this out. We also use AnalysisServices. </P><P>You asked a great question, what am I trying to show? I'm trying to find people that have moved from one level to another over time. It appears that most remain at the same level, but some move up, and others down. I'd really like to have the pattern continue over several reports or years, but one step at a time. :-).</P><P> </P><P>Thanks for attempt and much appreciated. I was able to generate my 'ideal' image by creating a small data set and doing what you did. Very intensive to do for each reporting period. Any more thoughts or ideas? I've watched many videos and read many forums and tried many measure codes, but I cannot get it to work for me. </P>Fri, 18 Dec 2020 13:12:43 GMThttps://community.powerbi.com/t5/Desktop/Using-a-Multiple-filters-and-a-Measure-as-the-Source-and/m-p/1559544#M636400SO2020-12-18T13:12:43Z