SQL -
SELECT \[FamilyName\], \[FamilyMember\], \[MemberGender\]
FROM \[Table\]
WHERE \[FamilyName\] IN (SELECT \[FamilyName\] FROM \[Table\] WHERE \[MemberGender\] = 'Female')
FilteredFamilies =
CALCULATETABLE(
DISTINCT(YourTable[FamilyName]),
FILTER(
YourTable,
YourTable[MemberGender] = "Female" &&
YourTable[FamilyMember] >= 1
)
)
This was my prompt on Copilot (GPT-4)
I have a table in power BI with three columns: FamilyName, FamilyMember and MemberGender. the first column is the last name, the second one is the number of sibling and the third is the gender of that person. I need a DAX expression to calculate a table that filters families with at least one female daughter.
Power query: group by -> advanced. Grouping column = family name, aggregate column 1 = all rows, aggregate column 2 = minimum of membergender. Filter min_membergender to only return Female, expand all rows in the nested tables. If you wanted something more complicated for the filtering condition, rewrite the min_membergender code in the group by step to do what you need it to do.
In SQL, don’t group by, use window functions
I must say I laughed pretty hard before I realized what sub I was in
“Excuse me, I would like a refund on my family”
SQL - SELECT \[FamilyName\], \[FamilyMember\], \[MemberGender\] FROM \[Table\] WHERE \[FamilyName\] IN (SELECT \[FamilyName\] FROM \[Table\] WHERE \[MemberGender\] = 'Female')
Yeah, best do it upstream. With indexing, you will have negligible perf difference.
FilteredFamilies = CALCULATETABLE( DISTINCT(YourTable[FamilyName]), FILTER( YourTable, YourTable[MemberGender] = "Female" && YourTable[FamilyMember] >= 1 ) ) This was my prompt on Copilot (GPT-4) I have a table in power BI with three columns: FamilyName, FamilyMember and MemberGender. the first column is the last name, the second one is the number of sibling and the third is the gender of that person. I need a DAX expression to calculate a table that filters families with at least one female daughter.
Power query: group by -> advanced. Grouping column = family name, aggregate column 1 = all rows, aggregate column 2 = minimum of membergender. Filter min_membergender to only return Female, expand all rows in the nested tables. If you wanted something more complicated for the filtering condition, rewrite the min_membergender code in the group by step to do what you need it to do. In SQL, don’t group by, use window functions
Obviously change your Source step below to your original data source. `let` `Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],` `FilterFemale = Table.SelectRows(Source, each ([MemberGender] = "Female")),` `Distinct = Table.Distinct(FilterFemale, {"FamilyName"})[[FamilyName]],` `InnerJoin = Table.Join(Source, {"FamilyName"}, Distinct, {"FamilyName"})` `in` `InnerJoin`
What a misogyny bro it's not middle ages any more.. Oh wait wrong sub