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