How would I write an expression for a slice to return data where no records are found in a child table?
Example:
I have two tables Personas and Notas.
Multiple Notas can be associated to a Person.
But a Person may not have any Notas.
In SQL, I would write it this way:
Select *
from Personas left outer join Notas on Personas.Persona ID = Notas.Persona ID
where Notas.Persona ID is null
@Nicholas_Christoffer You want to create a slice for the rows in Personas table where no Notas are associated for that row in Notas table, am I right?
Yes, that’s right
Do you have a REF column between them?
Yes, Persona ID is PK on Personas and FK on Notas Translate
@Nicholas_Christoffer I cannot understand from the screenshots which columns you have ref’d to the tables and what’s you sheet/column structure. So it’s hard to respond this way.
In the Notas table, if you have a column called Related Personas, try putting this in Show_If, =COUNT([_THIS])>0
@Bellave_Jayaram @Levent_KULACOGLU
thanks for the support and suggestions.
I figured it out:
Step 1:
define a virtual column on Personas with the count of related Notas.
Use this expression to count how many Notas records are associated with this row:
count(select (Notas[Persona], [Persona]=[_thisrow].[Persona ID]))
Step 2:
define a slice of Personas with the condition that the result of this virtual column is 0:
[Notas Relacionadas]=0 Translate
@Bellave_Jayaram @Levent_KULACOGLU
thanks for the support and suggestions.
I figured it out:
Step 1:
define a virtual column on Personas with the count of related Notas.
Use this expression to count how many Notas records are associated with this row:
count(select (Notas[Persona], [Persona]=[_thisrow].[Persona ID]))
Step 2:
define a slice of Personas with the condition that the result of this virtual column is 0:
[Notas Relacionadas]=0 Translate
@Nicholas_Christoffer , glad you figured it out👍
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |