Combining multiple measures into single measure and apply on the table

[Dunner2020]

Hi there,

I am a newbie, so accept my apology in advance.  I have a fact table related to sales. I have around five exclusion (data that does not consider ) rules for the data. I have created a measure for each exclusion rule. It means I have five measures. My measures looks like the following:

Rule 1  = COUNTROWS(FILTER('Sales','Sales'[Quantity] > 1) )

Rule 2 = COUNTROWS(FILTER('Sales','Sales'[NumberOfCustomers] <> 0) )

Rule 3 = COUNTROWS(FILTER('Sales','Sales'[Zone] <> "2" && 'Sales'[Zone] <> "6" && 'Sales'[Zone] <> "6" && 'Sales'[Zone] <> "8" ) )

Now I want to apply these rules into the main fact table to produce the filter view of the table. I have two questions:

1 ) I want to merge all these measures into a single measure and then apply single measure to the table. I am not sure how to merege measures into one. Is there any way to do that?

2) The goal of these measure to produce the table that filter out the data who met the conditions mentioned in the measures and shows remaining data of the table. I am not sure whether my created measure do the same or not. COUNTROWS would return the number of rows. Could any one tell me whether I have created the right measure or not?

[V-lianl-msft]

Hi @Dunner2020 ,

(1) To merge into one measure is to add all the filter conditions.Consider creating a table to write only values that you don't want to include.

(2) You can use the if statement to create a measure and then apply the measure to the visual level filter to filter the table.

Measure = IF(MAX(Sales[Quantity])>1&&MAX(Sales[NumberOfCustomers])<>0&&NOT(MAX(Sales[Zone]) in VALUES('Table'[zone])),1,0)

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dunner2020]

Thanks for your reply @V-lianl-msft . I think I may not able to present my problem clearly. I am displaying data (that comes from one table) using table visualization as shown in the picture:

Now I want to filter the data based on the number of customers i.e. I only want to display/filter the record based on the number of customers (i.e. only show the record where no of the customer is greater than 10). 

[V-lianl-msft]

Hi @Dunner2020 ,

Try to create a measure and apply it to visual level filter(measure greater than 10😞

measure = SUM(NumberOfCustomers)

See if it will help you.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Dunner2020 , Try like

COUNTROWS(FILTER('Sales','Sales'[Quantity] > 1 && 'Sales'[NumberOfCustomers] <> 0 && ('Sales'[Zone] <> "2" && 'Sales'[Zone] <> "6" && 'Sales'[Zone] <> "6" && 'Sales'[Zone] <> "8" ) ))

[Dunner2020]

@amitchandak Thanks for the reply. However, I said that I have 5 to 6 rules and I don't want to make it lengthy measure. Isn't possible to create seperate measures?

[amitchandak]

@Dunner2020 , when I use measure1 and Measure 2 in the display. The visual display is OR. means I will the values which will either have Measure 1 values or Measure 2 values. And Measure1 and measure2 will have blank values for the condition failed.

[Dunner2020]

@amitchandak  Sorry, I did not get you.