dax equivalent to select not In same table with where condition

[GURUPRASADB]

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]

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

[GURUPRASADB]

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]

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)

[GURUPRASADB]

Hi Vimal,

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

Here is the Sample data for the reference

ReportName	Activity	CreateDate
Backorder Dashboard	CreateReport	2/09/2019 0:00
Backorder Dashboard	CreateReport	12/09/2019 0:00
Backorder Dashboard	CreateReport	7/11/2019 0:00
Backorder Dashboard	DeleteReport	3/09/2019 0:00
BCP Standard Pricing	CreateReport	7/08/2019 0:00
BCP Standard Pricing	DeleteReport	7/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]

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

[GURUPRASADB]

Hi Vimal,

Here is the sample file. I have created measure called Reports and its still giving me error.

https://1drv.ms/u/s!Amhcm710yHadaTmw3Phya9mRzQ4?e=4PQ7tS  

[Anonymous · Accepted solution]

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

[GURUPRASADB]

Hi Vimal,

I created new calculated table as you said. I don't see any difference in results. Currently I still reports that are deleted. 

[GURUPRASADB]

Hi Vimal,

I got the issue. I was having "DeletedReports" as Filter Changed to "deletedreports". Its all working fine.

Thank you so much for the help. Much appreciated .

[Anonymous]

@GURUPRASADB Make sure you are pulling right fields in table. I don't see any of the delete report in new table. If you are using the same data which I have used then you should get 269 records in new table.

[GURUPRASADB]

Hi Vimal,

Could you share me report file which you are using. I am not sure what wrong I am doing here.