Forum Discussion

rizalm38's avatar
rizalm38
Frequent Visitor
5 years ago
Solved

Multiple Filter and multiple criteria

Hi BI Master(s),

I have a big tabular data set as shown below (table name = Cement):

SampleNameOxidesValue

Rawmeal

CaO50
ClinkerC3S62
ClinkerF.CaO1.2
KilnFeedAl2O38
RawmealFe2O35

I want to count the specific SampleName and Oxides combination with multiple criteria, the problem is I cannot combine multiple criteria from a single column.

Calculation step :

1. Counting and filter all data base on the given criteria for each Oxides.  (example : 

All Data =countrows(filter("C3S" &&"F.CaO")   <--- i made it simplestructure

DataIN=CALCULATE(

COUNTROWS('Cement');

FILTER('Cement';'Cement'[Oxides]="C3S" && 'Cement'[Value]>=58))

FILTER('Cement';'Cement'[Oxides]="F.CaO" && 'Cement'[Value]<=1,8))

2. After counting the particular data, I need to calculate the percentage of the counting result :

=calculate(DataIN/All Data) *100%

 

Kindly your helps Master

  • Hi rizalm38 ,

    You could try this way:

    DataIN = 
    CALCULATE(
    COUNTROWS('Cement'),
    ('Cement'[Oxides]="C3S" && 'Cement'[Value]>=58)||
    ('Cement'[Oxides]="F.CaO" && Cement[Value]<=1.8)
    )

     

2 Replies

  • Hi rizalm38 ,

    You could try this way:

    DataIN = 
    CALCULATE(
    COUNTROWS('Cement'),
    ('Cement'[Oxides]="C3S" && 'Cement'[Value]>=58)||
    ('Cement'[Oxides]="F.CaO" && Cement[Value]<=1.8)
    )

     

    • rizalm38's avatar
      rizalm38
      Frequent Visitor

      Thank you Payeras_BI , it's almost perfect solution but I need to use AND (&&) function instead OR (||). Maybe there is another way to solve my problem?