Forum Discussion

ruiner's avatar
ruiner
Frequent Visitor
3 years ago

Filtering based on parent grouping

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 nee...
Need Help
ruiner's avatar
ruiner
Frequent Visitor
3 years ago

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's avatar
v-yueyunzh-msft
Community Support
3 years ago

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's avatar
    ruiner
    Frequent Visitor
    3 years ago

    Hi, v-yueyunzh-msft 

     

    Here is a set of sample data for the Position table.

     

    EmployeeCodeEmployeeGroupIdEmployeeStatusIdFiscalPeriodIdCompanyIdId
    111111
    111112
    212113
    221114
    312115
    411116
    511117
    512118
    612119
    1221110

     

    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's avatar
      v-yueyunzh-msft
      Community Support
      3 years ago

      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's avatar
        ruiner
        Frequent Visitor
        3 years ago

        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.