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: select StoreName from DimStore where storename not in (select Storename from DimStore where Status='Closed')

 

  • 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)

     

11 Replies

  • 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)