Page 1 of 1

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

Posted: Wed Jun 20, 2018 4:16 pm
by Hazdiggity
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!

Re: Showing All Customers in a Table with Multiple Measures

Posted: Sun Jul 01, 2018 5:47 pm
by andredl
Is your customer table a separate lookup table or is it only part of your data table?

Re: Showing All Customers in a Table with Multiple Measures

Posted: Mon Jul 02, 2018 9:21 am
by Hazdiggity
The customer table is a separate lookup table

Re: Showing All Customers in a Table with Multiple Measures

Posted: Mon Jul 02, 2018 11:56 am
by andredl
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.

Re: Showing All Customers in a Table with Multiple Measures

Posted: Mon Jul 02, 2018 12:45 pm
by Hazdiggity
That does it! Thanks andredl - good trick!

Re: Showing All Customers in a Table with Multiple Measures

Posted: Mon Jul 02, 2018 12:53 pm
by andredl
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é