Filtering based on parent grouping

[ruiner]

I have a table laid out like this:

EmployeeId	CompanyId	FinancialPeriodId	GroupId	StatusId
1	10	5	1	1

My visual is grouped by company, financial period and then status. I need to have either slicers or filters on company, group and financial period.

The visual needs to display the number of unique employees within each status group. The part I'm having trouble with is an employee should be excluded from this count if they belong to more than one status. I also need to add an additional count of the number of employees excluded for having more than one status.

I currently have a computed table to count the number of statuses an employee has but it's not quite working correctly. I have also read that computed tables aren't responsive to slicers and I need the values to adapt to the slicer settings.

PositionByEmployee = 
    CALCULATETABLE(
        Position,
        REMOVEFILTERS(Position[StatusId])
    )

Headcount = 
    SUMMARIZE(
        PositionByEmployee, PositionByEmployee[CompanyId], PositionByEmployee[FinancialPeriodId], PositionByEmployee[EmployeeId],
        "StatusCount", DISTINCTCOUNT(PositionByEmployee[StatusId])
    )

Right now I have a foreign key into the Headcount table that is being used to filter the visual but I am not getting the expected numbers that I can get from a SQL query.

Thanks for your assistance!

[ruiner]

Thanks @v-yueyunzh-msft,

I have a computed column that combines the composite fk and have set the relationship between the tables manually.

The fields in the slicer are from tables related to the Positions table; they are filtering it correctly because I've added a count field to the visual and it is correct.

It is also the only visual at the moment so the only thing I can do with Edit Interactions is turn the slicers off for that one visual.

The numbers displayed in the visual are actually a bit lower than expected from the SQL query. I'm not sure that the computed tables are ungrouping by status id and regrouping by employee id correctly.

Thanks for the help

[v-yueyunzh-msft]

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

[ruiner]

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!

[v-yueyunzh-msft]

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

[ruiner]

Hi @v-yueyunzh-msft 

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.

[v-yueyunzh-msft]

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

[v-yueyunzh-msft]

Hi, @ruiner Based on your problem and description of the source data, it seems that you created two calculated tables using DAX and slicers for some dimensions: company, group, and financial period, but you find that the calculated table is not responding to the slicer and I need values to fit the slicer settings, right?

If that's the case, I think you can first check which table the dimensions you put in the slicer (company, group, and financial period) come from, and if it's from the table "PositionByEmployee" or the table "Position", I think you need to go to the data model page to check if the field you're currently placing in the visual is from the table "Headcount" that really has a relationship with the table in the slicer:

Because the correct inter-table relationship is the first prerequisite for your slicer to take effect, there is also a prerequisite that the interaction of your slicer needs to be enabled with other visuals, for this setting is in "Edit interaction", you can check this document for details:

Change how visuals interact in a report - Power BI | Microsoft Learn

If you can't find the root cause of the problem after checking, you can upload a download link to your test .pbix file (which does not contain any sensitive data) for further study.

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

[ruiner]

Hi, @v-yueyunzh-msft ,

Thank you very much for the detailed response. It worked as expected on the sample dataset, but unfortunately it does not give the same numbers as the query on the full dataset.

vs

The second image has the correct numbers. If you're unsure what could be causing this I can provide a larger sanitized dataset if you're able to look into this further for me.

Thanks!

[v-yueyunzh-msft]

Hi , @ruiner 

Thanks for your quick response!

I think it may be that there is some difference between my understanding and your needs. This is my undertand for your need:

If there is a problem with my understanding, you can point it out, and for your problem, you can provide me with a little more data, and then provide some detailed calculation process and results.

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

[ruiner]

Hi @v-yueyunzh-msft ,

It was actually a problem with my slicers that I have fixed. I am still having a problem however. When multiple selections are present on the slicers the totals are cumulative instead of being per group.

Do you have any ideas how to correct this?

Thanks for your help!

[v-yueyunzh-msft]

Hi , @ruiner 

If you want the "total" show right, you can create a new measure based on the before, like this:

Measure 2 = var _t =  ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Query1'),'Query1'[CompanyId],'Query1'[FiscalPeriodId],'Query1'[EmployeeStatusId]),"measure",[Measure])
return
IF(HASONEVALUE('Query1'[EmployeeStatusId]),[Measure],SUMX(_t,[measure]))

Then you can test in the visual :

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

[ruiner]

Thanks again @v-yueyunzh-msft. That wasn't the problem I was looking at yet but that did fix that issue.

What is happening is when the slicer is set to fiscal year 2021 the correct totals show

And when set to 2020 the correct totals are also shown

The problem is when both 2020 and 2021 are selected at the same time

The same thing happens when multiple values are selected for other slicers, such as the company slicer.

[v-yueyunzh-msft]

Hi , @ruiner 

It seems the [Measure 2] returns the 11+15 and 26+29 , can you check the summarize() function contains the all columns in your table?

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

[ruiner]

Hi @v-yueyunzh-msft,

The original measure also returns the same incorrect numbers when multiple slicer values are selected.