Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

CountRows measure with multiple filter conditions based on columns in different tables

Hi All,   I am just trying to work out the basic syntax and function rules for creating a measures to count rows that meet 2 different filter conditions based on two columns in different tables. As...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

    You may try below measure. Attached the simplified sample file for your reference. Let me know if it matches your request.

    Measure =
COUNTROWS (
    CALCULATETABLE (
        'Trusts_WiFi ImpProg CRM',
        FILTER (
            'Trusts_WiFi ImpProg CRM',
            'Trusts_WiFi ImpProg CRM'[cc_progressindex] > 3
        ),
        FILTER (
            'Trusts WiFi Dashboard Core Data',
            'Trusts WiFi Dashboard Core Data'[Rollout Priority] = "Stage3(Wider rollout)"
        )
    )
)

    Regards,

    Cherie

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-cherch-msft ,

 

Thanks for the reply. Whilst the data is not sensitive it is work related so I would rather not upload the pbix file. I am happy to answer any questions you may have regarding my issue and do any troubleshooting on proposed solutions or corrections. Thanks to the links you sent me I believe RELATE is not suitable for use in Many to 1 related tables which is what I have (see below)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

My goal is unchanged (see earlier post) which is to create a measure that returns a count of the number of rows that meet multiple criteria based on the value of Rollout Priority (type text) in one table and cc_progressindex (type whole number) in another related table (see diagram above)

 

The two criteria as individual measures both work

 

C1 = Countrows(FILTER('Trusts WiFi Dashboard Core Data',[Rollout Priority]="Stage3 (Wider Rollout)"))

C2 = COUNTROWS(FILTER('Trusts_WiFi ImpProg CRM',[cc_progressindex]>3))

 

My problem is combining them into a single countrow measure that evaluates both criteria to return a count. I have tried creating measures along the lines of:

 

M1 = (Countrows(FILTER('Trusts_WiFi ImpProg CRM',([cc_progressindex])>3))&&(RELATEDTABLE('Trusts WiFi Dashboard Core Data'),[Rollout Priority]>3))
 
Which gives an error: Operator or expression '( )' is not supported in this context. Whatever that means
 
and
 
M1 = Countrows(FILTER('Trusts WiFi Dashboard Core Data',[Rollout Priority]="Stage3 (Wider Rollout)"))&&FILTER('Trusts_WiFi ImpProg CRM',[cc_progressindex]>3)
 
Which gives an error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
 
Any ideas on taking this forward?
 
Many Thanks 
 
Howard
 
Howard

 

 

v-cherch-msft's avatar
v-cherch-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous

 

You may try below measure. Attached the simplified sample file for your reference. Let me know if it matches your request.

Measure =
COUNTROWS (
    CALCULATETABLE (
        'Trusts_WiFi ImpProg CRM',
        FILTER (
            'Trusts_WiFi ImpProg CRM',
            'Trusts_WiFi ImpProg CRM'[cc_progressindex] > 3
        ),
        FILTER (
            'Trusts WiFi Dashboard Core Data',
            'Trusts WiFi Dashboard Core Data'[Rollout Priority] = "Stage3(Wider rollout)"
        )
    )
)

Regards,

Cherie

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-cherch-msft

     

    Apologies for delayed reply I only have one excuse .. Christmas!

     

    So I tried your solution and it worked straight away. I am still learning DAX and BI but I think I will stop trying to code functions in one big linear string in BI and use the structured approach in the code snipett you sent through. So just to check my understanding the CALCULATETABLE function returns the one argument needed by the COUNTROWS function and the CALCULATETABLE has one Table expression and 2 filters within it. I think I was making a real hash of the syntax but thank you so much for persevering with the dialogue, I will mark your solution as the accepted solution and hope you have a great New Year,

     

    All the Best

     

    Howard

  • thedesk's avatar
    thedesk
    New Member
    5 years ago

    Hi there, I follow the example given and the results in the cells are correct, however, the Totals do not add up....

     

    My DAX is below base don the above example...

     

    =COUNTROWS(CALCULATETABLE(data,

    FILTER(data,data[Cost]<>MIN(d_Courses[1 course £net]) && data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[2 course £net]) && data[Attend #]>0),FILTER(data,data[Cost]<>MIN(d_Courses[3 courses £net]) && data[Attend #]>0)

    ))