Forum Discussion
Filtering based on parent grouping
The slicer in that example is set to [EmployeeGroupId]=1, it is filtering out the records where the group is 2.
Here is the SQL query I am trying to replicate if that helps.
select
CompanyId,
FiscalPeriodId,
EmployeeStatusId,
count(distinct EmployeeCode) Total
from
(
select
CompanyId,
FiscalPeriodId,
EmployeeCode,
iif(
(
select
count(distinct p2.EmployeeStatusId)
from
PositionSample p2
where
p2.FiscalPeriodId = p.FiscalPeriodId and
p2.CompanyId = p.CompanyId and
p2.EmployeeGroupId = p.EmployeeGroupId and
p2.EmployeeCode = p.EmployeeCode
) = 1,
p.EmployeeStatusId,
null
) as EmployeeStatusId
from
PositionSample p
where
p.EmployeeGroupId = 1 and
p.FiscalPeriodId in (1) and
p.CompanyId in (1)
group by
CompanyId,
FiscalPeriodId,
EmployeeCode,
EmployeeStatusId
) sub
group by
CompanyId, FiscalPeriodId, EmployeeStatusId
;
The status id is unchanged if the employee only belongs to one status. They may have more than one position within this status and it will still be unchanged.
The status id is null in the query if the employee holds positions under more than one status within the filter set. I would like to rewrite the status to a different id in DAX.
Hi , ruiner
Thanks for your quick response and your sql to help us undertand your need ! You want to add a row as null in the visual . This is hard in power BI to add a row in the visual without data .So we need to add a null as a row in the table.
Here are the steps you can refer to :
(1)This is my test data:
(2)We need to add some rows in Power Query Editor, We can add a blank Query and enter this in "Advanced Editor":
let
Source = Table.Group(Table,{"CompanyId","FiscalPeriodId","EmployeeGroupId"},{"EmployeeStatusId",(x)=>"null"}),
#"Added Custom" = Table.AddColumn(Source, "Id", each List.Max(Table[Id])+1 ),
#"Appended Query" = Table.Combine({#"Added Custom", Table})
in
#"Appended Query"Then we can get this table:
(3)Then we can apply the data to Desktop and create a measure in Power BI Desktop:
Measure =
var _t =FILTER( ALLSELECTED('Query1') , 'Query1'[EmployeeStatusId]<> "null")
var _t2 =ADDCOLUMNS(_t , "code_count" , var _code= [EmployeeCode] return COUNTROWS( DISTINCT(SELECTCOLUMNS( FILTER(_t , [EmployeeCode]=_code) ,"status",[EmployeeStatusId]))))
var _status_null =DISTINCT(SELECTCOLUMNS( FILTER(_t2 , [code_count]<>1) ,"employeeCode",[EmployeeCode]))
var _t3= FILTER(_t2 , [code_count] =1)
var _curstatus = MAX('Query1'[EmployeeStatusId])
return
IF(MAX('Query1'[EmployeeStatusId])="null",COUNTROWS(_status_null) ,COUNTROWS( DISTINCT(SELECTCOLUMNS( FILTER(_t3,'Query1'[EmployeeStatusId]=_curstatus) ,"employeeCode",[EmployeeCode])) ) )(4)Then we can put this measure on the visual and we can get the result you provide:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly