Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 for each question on the bottom tile. 

 

the other tiles have filtering enabled and drillthrough. The pie chart is count by region and since I unpivoted all the questions it created alot of extra rows and the count of region is way off. Is there a way to not count those for that tile only? 

 

unpivot.png

 

Thanks!

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

I can reproduce your problem

12.pngT

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]))

13.png

 

Best Regards

Maggie

 

View solution in original post

9 REPLIES 9
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

I can reproduce your problem

12.pngT

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]))

13.png

 

Best Regards

Maggie

 

Thank you so much for providing this solution. Works perfectly. 

Anonymous
Not applicable

@v-juanli-msft

 

Thank you! That worked out great.  I really appreciate your help! Smiley Happy

v-juanli-msft
Community Support
Community Support

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
Not applicable

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. 

 

unpivoted dataset.png

Dashboard data: 

dashboard data.png

 

Here is a comparison on the count difference:

 

pie chart differences.png

 

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

Greg_Deckler
Super User
Super User

Sounds like you perhaps need a measure does does a COUNT but then divides that number by the COUNT of the DISTINCT (or VALUES) number of questions?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler Thanks for the quick response! 

 

You are saying doing a count on the values (responses) and then divide by the count(distinct) of questions? 

 

Thanks!

That was the idea, yes

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler I tried:

 

Count of Value divided by Count of Attribute =
DIVIDE(COUNTA('WIOA'[Value]), DISTINCTCOUNT('WIOA'[Attribute]))
 
but the count is not correct, im comparing the data count and its still way off. Closer number but still not right.  
 
Do you have any other ideas? Thanks for the help. 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.