Forum Discussion
Filtering based on parent grouping
Hi , ruiner
This seems to be a DAX question, can you provide more sample data and then provide us with the result data you ultimately want to get on the visual as tabular form ？
If you're filtering by slicers, you can't use a new table, you need to do so with measures. And if you place some fields on the visual, the calculation occurs in the filtered context of the placed field.
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
Hi, v-yueyunzh-msft
Here is a set of sample data for the Position table.
| EmployeeCode | EmployeeGroupId | EmployeeStatusId | FiscalPeriodId | CompanyId | Id |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 2 |
| 2 | 1 | 2 | 1 | 1 | 3 |
| 2 | 2 | 1 | 1 | 1 | 4 |
| 3 | 1 | 2 | 1 | 1 | 5 |
| 4 | 1 | 1 | 1 | 1 | 6 |
| 5 | 1 | 1 | 1 | 1 | 7 |
| 5 | 1 | 2 | 1 | 1 | 8 |
| 6 | 1 | 2 | 1 | 1 | 9 |
| 1 | 2 | 2 | 1 | 1 | 10 |
The results from the visual for this sample data look like this. The slicer should be filtering out employee group 2.
DistinctEmployeeCount is produced by this measure.
DistinctEmployeeCount =
CALCULATE(
COUNT(PositionSample[EmployeeCode]),
FILTER(
PositionSample,
RELATED(Headcount[EmployeeStatusCount]) = 1
)
)
And finally, here are the expected results.
Thanks for your help!
Hi, ruiner
Thanks for your quick reponse!
I'm very sorry that there is no way to understand your arithmetic logic.
Accoridng to the sample data , when you filter the [employee group]=2 , it shows the table like this:
And then the [DistinctEmploymentCount] measure seems to calculate the sum of the "EmployeeCode" where the [EmployeeStatusCount]=1 in the 'Headcount' Table.
And from your sample data , i still do not understand how to get the table when filter the [employee group]=2?
Can you give me the sample data in 'Headcount' Table and explain the detailed calculation for the table you want to get in the end?
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
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