cancel
Showing results for
Did you mean:

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

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?

Thanks!

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous

T

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

then create a measure to calculate the count of regions

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

Best Regards

Maggie

9 REPLIES 9
Community Support

Hi @Anonymous

T

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

then create a measure to calculate the count of regions

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

Best Regards

Maggie

Frequent Visitor

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!

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.

Dashboard data:

Here is a comparison on the count difference:

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

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?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
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!

Super User

That was the idea, yes

Become an expert!: Enterprise DNA
External Tools: MSHGQM
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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors