measure as a filter

[curtisk]

Hello,

I am stuck on the following problem. Please help.

Table 1 contains:

My sales data is from a measure - from Table 2 (which I am able to do) is like:

My desired output is showing count of device at shop that has no sales.

I was trying to put into a matrix, but as soon as I put shop as row and even sales =0 as filter, all shops data shows regardless has sales or not.

Any help is appreciated.

[Phil_Seamark · Accepted solution]

Hi @curtisk

I've updated the PBIX file from that simple model and have it working as I think you need.

Bascially I've added a +0 to the end to the end of the sales measure and also was careful about which shop field to use in my matrix.  I use the field from Table2 rather than Table1

https://1drv.ms/u/s!AtDlC2rep7a-oBnYHCJluU2V97bz

[Phil_Seamark]

HI @curtisk

I managed to get it to work as fas as I understand you need to.

Is the Sales column in Table 2 and actual column or a calculated measure?

Here is the very basic PBIX File I mocked up

https://1drv.ms/u/s!AtDlC2rep7a-oBnYHCJluU2V97bz

[curtisk]

Thanks for your reply.

Sales info is a calculated measure from another table. (Basically, it is just sum of sales group by Shop to be shown).

[Phil_Seamark · Accepted solution]

Hi @curtisk

I've updated the PBIX file from that simple model and have it working as I think you need.

Bascially I've added a +0 to the end to the end of the sales measure and also was careful about which shop field to use in my matrix.  I use the field from Table2 rather than Table1

https://1drv.ms/u/s!AtDlC2rep7a-oBnYHCJluU2V97bz

[curtisk]

Thanks.

I am a bit confused with your 3 tables approach now.

Basically, I have Sales and Table 1, I am not sure how to create Table 2 and what is the purpose of it.

Is it possible to get the same result with only Sales and Table 1?

[Phil_Seamark · Accepted solution]

I use [Table 2] to effectively operate as a dimenstion table (one row per shop and now more).  Which allows me to create a relationship with [Table1] and [Sales] that have many lines for each shop.

Otherwise you can't have a relationship between [Sales] and [Table2] and need to rely on some pretty in-efficient methods to create the calcualtion.