Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Text Box Values not Recognizing Measures that Contain filters

I am trying to insert a text value into the text box and it is not picking up the measure when it contains filtering within the calculation. 

 

This measure is recognized/picked up and generates the correct value. 

Text_HC%ChangeYoY_Global =
VAR PercentChange = [_HC%ChangeYoY_Global]

RETURN
    SWITCH( True (),
        PercentChange >= 0, "increased by " & CONCATENATE( ROUND( PercentChange * 100, 1), "%"),
        PercentChange < 0, "decreased by " & CONCATENATE( ROUND ( PercentChange * -100, 1), "%"),
        PercentChange = BLANK(), "<no change>",
        BLANK() )
 
However, when I try to insert the same measure, with the base measure containing a filter for home country = USA it will not pick up the measure. 
 
The branches are as follows: 
_HC USA =
VAR MinDate = MIN( Calendar_FY_To2025[Fiscal_BOP] )
VAR MaxDate = MAX( Calendar_FY_To2025[End of Month] )
VAR Headcount =
    CALCULATE(
        COUNTROWS( Actives_Mthly ),
        FILTER(
            VALUES( Actives_Mthly[File.Capture.Date] ),
            Actives_Mthly[File.Capture.Date] = MaxDate
        ),
        FILTER( 'Home Country DIM',
            'Home Country DIM'[Global_Label] = "USA" )
    )
RETURN
    Headcount
 
_HC USA PY =
VAR MinDate = MIN( Calendar_FY_To2025[Fiscal_BOP - 1] )
VAR MaxDate = MAX( Calendar_FY_To2025[End of Month LY] )
VAR Headcount =
    CALCULATE(
        COUNTROWS( Actives_Mthly ),
        FILTER(
            VALUES( Actives_Mthly[File.Capture.Date] ),
            Actives_Mthly[File.Capture.Date] = MaxDate
        ),
        FILTER( 'Home Country DIM',
            'Home Country DIM'[Global_Label] = "USA" )
    )
RETURN
    Headcount
 
_HC%ChangeYoY_USA = DIVIDE( [_HC USA], [_HC USA PY] ) - 1
 
The measure I am trying to add for the value is: 
Text_HC%ChangeYoY_USA =
VAR PercentChange = [_HC%ChangeYoY_USA]

RETURN
    SWITCH( True (),
        PercentChange >= 0, "increased by " & CONCATENATE( ROUND( PercentChange * 100, 1), "%"),
        PercentChange < 0, "decreased by " & CONCATENATE( ROUND ( PercentChange * -100, 1), "%"),
        PercentChange = BLANK(), "<no change>",
        BLANK() )
 
 
Every additional value I need for the text boxes will also contain some filtering. Is this a bug? Are there any workarounds? I tried leveraging the synonyms and the teach options but it does not help.

The report page has a Period Actualized filter and is a summary with text boxes, and no visuals. Using the cards is not an option due to the custom formatting of the page and content. 
 
The working measure generates the value: increased 6.8%
The measure that is not working should generate the value: increased 12.6%
 
Can someone please help?
Need Help

2 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    2 years ago

    Hello Anonymous ,

     

    In order to do the headcount calculation using Power BI, you need the following two tables in your data model.  

    - Employee table

    - Calender table

     

    The Employee table contains as a minimum 3 pieces of information below, but if you want to add a geographycal analysis, you can add that information such as USA, Canada etc in the employee table, too. 

    1. Employee ID
    2. Hiring date
    3. Leaving date

    Then you will have these two tables as "disconnected tables" in your data model and write a dax formula like below, in order to identify flexibly who are the employees still employed at any particular point in time.  

    After getting the flexible and versatile headcount measure working which respects time dimension, you can write a ConcatentateX measure to prepare the variance analysis commentary as you required.  There's an article on how to prepare headcount analysis using Power BI in the link below:

    https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/

    Good luck!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi. My measure is fine and working when used in other visuals or cards. But its not accepting it in the text value. We build our time intelligence using the variables so none of our tables are connected to the date table. I have the concatenate set up as such: 

      VAR PercentChange = [_HC%ChangeYoY_USA]

      RETURN
          SWITCH( True (),
              PercentChange >= 0, "increased by " & CONCATENATE( ROUND( PercentChange * 100, 1), "%"),
              PercentChange < 0, "decreased by " & CONCATENATE( ROUND ( PercentChange * -100, 1), "%"),
              PercentChange = BLANK(), "<no change>",
              BLANK() )