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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

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

5 REPLIES 5
Anonymous
Not applicable

I have a Matrix that has total coming from backend. when i use that field matrix give me sum of rows which i need to get rid

 

this is the data

blackbranded_0-1674680725193.png

 

Which delivery name is blank this is the main project value

so when i use matris its giving me like this which wrong

blackbranded_1-1674680804013.png

 

2880 is the correct value but when i do sum its include i need exclude that value

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

 

 

 

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

 

 

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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

Top Solution Authors