Forum Discussion

lyonslat's avatar
lyonslat
Frequent Visitor
6 years ago

Calculate with multiple criteria

Hi All,    First post, so apologies if it doesn't make a huge amount of sense, but I'm really hoping someone can help me.    Relatively new to DAX, but very familiar with Excel, so most of my log...
Anonymous's avatar
Anonymous
Not applicable
6 years ago
Calculate (Distinctcount(table[column]),filter(table,containstring(table[columm_name],"food") || containstring(table[columm_name],"beer")))

Thanks
Pravin

If it resolves your problem mark it as a solution and give Kudos
  • lyonslat's avatar
    lyonslat
    Frequent Visitor
    6 years ago

    Hi Pravin, 

     

    Thanks for your input, it's much appreciated. 

     

    Unfortunatley this didn't quite work the way I expected it to. 

     

    The result I'm looking for is to show the number of transactions that contain both Food and Beer. The result that your suggestion put forward, gives me a number of the transactions that contain either food or beer and totals them up. 

     

    Any suggestions?

     

    Thanks, 

    Ben 

    • lyonslat's avatar
      lyonslat
      Frequent Visitor
      6 years ago

      The best way I can describe this is if you imagine a venn diagram of transactions.

       

      One side has food transactions, the other has beer, and in the middle you have the number of distinct transactions that contain both food and beer. 

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi lyonslat 

       

      Create below measure

       

      Flag =
      var food=CALCULATE(COUNT(Sheet1[profit_center_id]),allexcept(Sheet1,Sheet1[profit_center_id]),Sheet1[revenue_category_name]="FOOD")
      Var beer=CALCULATE(COUNT(Sheet1[profit_center_id]),allexcept(Sheet1,Sheet1[profit_center_id]),Sheet1[revenue_category_name]="BEER")
      return
      IF(food>0 && beer>0,1,0)
       
      And add this measure to visual level filter of table and set it to 1.
       
       
       
      Thanks & regards,
      Pravin Wattamwar
      www.linkedin.com/in/pravin-p-wattamwar

      If I resolve your problem Mark it as a solution and give kudos.
      • HotChilli's avatar
        HotChilli
        Icon for Community Champion rankCommunity Champion
        6 years ago

        You could use INTERSECT in the following

        MeasureFoodAndBeer = 
VAR _FoodChecks = CALCULATETABLE (VALUES ( TableBill[check_number] ), TableBill[revenue_category_name] = "Beer")
VAR _BeerChecks = CALCULATETABLE (VALUES ( TableBill[check_number] ), TableBill[revenue_category_name] = "Food")

RETURN
 COUNTROWS(INTERSECT(_BeerChecks, _FoodChecks))

        If you want to break this down and test it, you can materialise each of the tables by creating a table (New Table) with the CALCULATETABLE code. You can then look at the table in data view.