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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 created new calculated table as you said. I don't see any difference in results. Currently I still reports that are deleted. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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