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

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?
      .