Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

not count unpivot extra rows

Hello,   I need to know how to not count the extra rows power bi creates when I unpivot multiple columns?    Im working on survey data so all the questions I am unpivoting to get the responses fo...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    I can reproduce your problem

    T

    To solve this, insert a step before "Unpivot columns"

    add index columns from 1

    then create a measure to calculate the count of regions

    Measure = CALCULATE(DISTINCTCOUNT(Sheet5[Index]),ALLEXCEPT(Sheet5,Sheet5[region]))

     

    Best Regards

    Maggie

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous

Could you show an example dataset after unpivoting columns?

Also, please change the pie chart and chart on bottom to table visual so i can see what data is in the visual.

 

Best Regards

Maggie

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hello v-juanli-msft Thanks for the reply! 

 

Here the example of the unpivoted dataset:
This has a total of 515 rows and after unpivoting it the row count gets to 7493. Total of 65 questions and their responses. 

 

Dashboard data: 

 

Here is a comparison on the count difference:

 

 

Let me know if you need anything else. Thanks again!