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

matrix visual - do not show total for one column

I have a table visual with numerical columns and a date column and a total line.

The total for the date column shows the latest date which is not helpful.

The matrix is filtered, but does not have a slicer.

 

I have changed the color of the total so it's mostly invisible, but would like to not show it at all. 

 

Ana Maria Bisbe York presented a solution in her DAX webinar but I didn't understand it fully and it seemed too complex for my problem. (https://www.youtube.com/watch?v=2Des1Id-22A from 19:45 min)

Any ideas would be welcome. I'm new to power BI and don't really have a local community to consult with. 

2 REPLIES 2
VasTg
Memorable Member
Memorable Member

@avitalo1 

See the image below. The metric "Measure" was not calculated for Total line. How do I identify the total line? You need to understand how filter context works. On a high level each cell is calculated independently in a matrix. In the example,

for the first line, the table "Table" is filtered for date 01/01/2020 and sums the sales column for the returned rows. 

for the second line, the table "Table" is filtered for date 01/05/2020 and sums the sales column for the returned rows and so on. So each row has a date filter which filters  the table "Table" based on defined relationship. But the total line does not have any date filter. When the condition fails in HASONEFILTER, it just blanks out based on the measure formula.

 

image.pngRead this article to better understand the concept.

 

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

 

If it helps, mark it as a solution

Kudos are nice too.

 

 

Connect on LinkedIn
avitalo1
Frequent Visitor

@VasTg 

Thank you for the through explination and the example.

 

If I understand correctly I need to replace my current Date of registrtion request column with the measure:

2nd Date of registration request = IF(HASONEFILTER('Rama + Adi merged'[ shem_fakulta_1-EN]]),[missing second argument] , BLANK())
 
All the other columns in this matrix are based on measures and I they don't appear on the drop down for the SUM function, what DAX function can I use instead of SUM which requires columns? Or is there a way to sum all except the one date column?
 
Attaching a screenshot of my matrix columns - the one I don't want to total is the only date. matrix headings.png

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors