[Solved] Showing All Customers in a Table with Multiple Measures

Used for anything related to the new Power BI Desktop and Service tools
Hazdiggity
Posts: 5
Joined: Wed Jun 20, 2018 4:04 pm

[Solved] Showing All Customers in a Table with Multiple Measures

Postby Hazdiggity » Wed Jun 20, 2018 4:16 pm

Hi all,

I have a Power BI Desktop report that contains:
- Several slicers such as "Financial Year", "Product Type","Region"
- A Table listing customers and a number of measures such as "Total Transactions", "Avg Purchase Amount", "User Rating"

The table only displays the customers who were active in the Financial Year selected in the slicer i.e. customers for whom there is transactional data in that financial year.

However, I would like to also be able to see all customers, including those who were inactive and didn't transact at all in the selected Financial Year.

Basically, I'd like the "Customer" field to ignore the filter context of the "Financial Year" slicer but for all other measures to retain all filter and row contexts.

I have tried to "show items with no data" but that doesn't do it. Does anyone have any ideas?

Thanks!
Last edited by Hazdiggity on Mon Jul 02, 2018 1:31 pm, edited 1 time in total.

andredl
Posts: 59
Joined: Wed Sep 23, 2015 9:56 pm

Re: Showing All Customers in a Table with Multiple Measures

Postby andredl » Sun Jul 01, 2018 5:47 pm

Is your customer table a separate lookup table or is it only part of your data table?

Hazdiggity
Posts: 5
Joined: Wed Jun 20, 2018 4:04 pm

Re: Showing All Customers in a Table with Multiple Measures

Postby Hazdiggity » Mon Jul 02, 2018 9:21 am

The customer table is a separate lookup table

andredl
Posts: 59
Joined: Wed Sep 23, 2015 9:56 pm

Re: Showing All Customers in a Table with Multiple Measures

Postby andredl » Mon Jul 02, 2018 11:56 am

Just an idea, but if you were to add a measure to your matrix, call it Test = ISFILTERED(CustomerTable[Customers]), does that give you the behaviour you want? It should add a column showing TRUE next to all the customers in your lookup table, but the slicers will still work.

Hazdiggity
Posts: 5
Joined: Wed Jun 20, 2018 4:04 pm

Re: Showing All Customers in a Table with Multiple Measures

Postby Hazdiggity » Mon Jul 02, 2018 12:45 pm

That does it! Thanks andredl - good trick!

andredl
Posts: 59
Joined: Wed Sep 23, 2015 9:56 pm

Re: Showing All Customers in a Table with Multiple Measures

Postby andredl » Mon Jul 02, 2018 12:53 pm

Hazdiggity wrote:That does it! Thanks andredl - good trick!

You're welcome :)
Would you mind editing the topic text to include the word [Solved] to indicate it has been solved?
Cheers,
André


Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 2 guests

cron