Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

dax equivalent to select not In same table with where condition

Hi All, I have table that has log that has records for same stores which are open,closed. I want to select stores which are not closed   I am looking for Dax equivalent  for following query SQL: s...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous 
    You need to create a calulated table not a measure

    Go to modeling tab

    click on new table 

    and paste following formula

    Reports =

VAR _values = CALCULATETABLE(VALUES(PowerBIAuditLog[ReportName]),PowerBIAuditLog[Activity]="DeleteReport")
RETURN
CALCULATETABLE(VALUES(PowerBIAuditLog[ReportName]),NOT(PowerBIAuditLog[ReportName]) IN _values)

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Anonymous  Create a calculated table with following syntax and check if it works for you

 

Table 2 = 
VAR _values = CALCULATETABLE(VALUES('Table'[Store]),'Table'[Status]="Close")
RETURN CALCULATETABLE(VALUES('Table'[Store]),NOT('Table'[Store]) IN _values)

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Vimal,

I am getting this error

" A table of multiple values was supplied where a single value was expected."

This my actual dax query: 

Reports = VAR _values = CALCULATETABLE(VALUES(PowerBIAuditLog[ReportName]),PowerBIAuditLog[Activity]="DeletedReport")
RETURN CALCULATETABLE(VALUES(PowerBIAuditLog[ReportName]),NOT(PowerBIAuditLog[Activity]) IN _values)
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    In not operator you need to pass report name not activity
    Reports =
    VAR _values = CALCULATETABLE(VALUES(PowerBIAuditLog[ReportName]),PowerBIAuditLog[Activity]="DeletedReport")
    RETURN CALCULATETABLE(VALUES(PowerBIAuditLog[ReportName]),NOT(PowerBIAuditLog[ReportName]) IN _values)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Vimal,

      I am getting same error after changing the formula to Reportname. 

      Here is the Sample data for the reference

      ReportNameActivityCreateDate
      Backorder DashboardCreateReport2/09/2019 0:00
      Backorder DashboardCreateReport12/09/2019 0:00
      Backorder DashboardCreateReport7/11/2019 0:00
      Backorder DashboardDeleteReport3/09/2019 0:00
      BCP Standard PricingCreateReport7/08/2019 0:00
      BCP Standard PricingDeleteReport7/08/2019 0:00

       

      DAX Formula:

      Reports =

      VAR _values = CALCULATETABLE(VALUES(PowerBIAuditLog[ReportName]),PowerBIAuditLog[Activity]="DeletedReport")
      RETURN
      CALCULATETABLE(VALUES(PowerBIAuditLog[ReportName]),NOT(PowerBIAuditLog[ReportName]) IN _values)
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous  I'm able to create table with the sample data you have provided and as per your logic I'm getting zero record

         

        One more thing in sample data in activity column your value is DeleteReport whereas in your formula you have written DeletedReport. Is it possible to share pbix after masking sensitive data?
        .