cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tps136
Frequent Visitor

Remove BLANK rows from Matrix which has full of Measures

Hi All,

 

I've a Matrix that has full of Mesaures grouped by Items, Price. In a specific measure I have blank rows. I need them to hide,

any help please?

 

Result that i am getting now,

Item     Price     Sell%     Totalamt     TotUnits

101       $106      61%       $10,000        500

102       $211      55%       $12,500        750

103       $099                    $10,345        325

104       $345                    $16,111        720

 

Result I want is just first 2 rows,

Item     Price     Sell%     Totalamt     TotUnits

101       $106      61%       $10,000        500

102       $211      55%       $12,500        750

 

DAX

Sales Rank Desc = RANKX(ALL(Sheet1[Item], Sheet1[Price]),[Total Sell%],,DESC)

Sell% = IF([Sales Rank Desc] <= [SelectedTopNValue],[Total Sell%], BLANK())

 

Thanks is advance! SenSam

2 ACCEPTED SOLUTIONS
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @tps136,

You can just add in Visual Filters of that Visual, as "is not blank".

 

That would satisy your requirement. Same is shown below

 

The First Visual shows the full data while the second visual has Visual Filters applied to it

 

First Visual is simply full data display and the second visual is having Visual Filters appliedFirst Visual is simply full data display and the second visual is having Visual Filters applied

 

 

 

View solution in original post

Thejeswar
Resident Rockstar
Resident Rockstar

Hi @tps136,

I think you should modify your RANKX DAX a bit at the end

 

The Same is shown below.

 

Sales Rank Desc = RANKX(ALL(Sheet1[Item], Sheet1[Price]),[Total Sell%],,DESC, DENSE)

This should bring in the other record as well

View solution in original post

4 REPLIES 4
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @tps136,

You can just add in Visual Filters of that Visual, as "is not blank".

 

That would satisy your requirement. Same is shown below

 

The First Visual shows the full data while the second visual has Visual Filters applied to it

 

First Visual is simply full data display and the second visual is having Visual Filters appliedFirst Visual is simply full data display and the second visual is having Visual Filters applied

 

 

 

tps136
Frequent Visitor

Wow! Fantastic!  Thanks a lot.

 

One more request need your help. I've sell%  --> 65% that comes twice for 2 different items. while displaying  top3 I should have  4 rows counting 65% as one of the top.

 

Result that i am getting now [when i select top3]

Item     Price     Sell%     Totalamt     TotUnits

101       $106      61%       $10,000        500

102       $211      61%       $12,500        750

103       $099      55%       $10,345        325

 

Expected Result: [First 2 rows considred as top = 1, 2rd row = top2, 3rd row being top3]

Item     Price     Sell%     Totalamt     TotUnits

101       $106      61%       $10,000        500

102       $211      61%       $12,500        750

103       $099      55%       $10,345        325

104       $345      52%       $16,111        720

 

 

Thejeswar
Resident Rockstar
Resident Rockstar

Hi @tps136,

I think you should modify your RANKX DAX a bit at the end

 

The Same is shown below.

 

Sales Rank Desc = RANKX(ALL(Sheet1[Item], Sheet1[Price]),[Total Sell%],,DESC, DENSE)

This should bring in the other record as well

tps136
Frequent Visitor

It worked! Thats a great Help! Thanks again ..

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!