cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
curtisk Regular Visitor
Regular Visitor

measure as a filter

Hello,

I am stuck on the following problem. Please help.

Table 1 contains:

Capture1.PNG

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

Capture3.PNG

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

Capture2.PNG

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.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: measure as a filter

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark Super Contributor
Super Contributor

Re: measure as a filter

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.

 

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

5 REPLIES 5
Phil_Seamark Super Contributor
Super Contributor

Re: measure as a filter

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

curtisk Regular Visitor
Regular Visitor

Re: measure as a filter

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 Super Contributor
Super Contributor

Re: measure as a filter

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
curtisk Regular Visitor
Regular Visitor

Re: measure as a filter

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 Super Contributor
Super Contributor

Re: measure as a filter

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.

 

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!