measure as a filter

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
8 years ago
Hi Anonymous

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
8 years ago
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.

Forum Discussion