Forum Discussion

Txtcher's avatar
Txtcher
Icon for Helper V rankHelper V
1 year ago
Solved

Field Parameter Contains Field Also Used in Measure

I am still pretty new at Power Bi and am not a professionally trained analyst, so my apologies in advance.
I am creating a report to track backlog complaints. The backlog complaint counts are sliced using 3 fields: Region, Program & Priority. I created a field parameter for these fields.
One of the measures I created is a count filtered to include specific programs. 

LOF Backlog = 
CALCULATE(
    [Backlog],
      'RS Cases'[Program] IN {"Assisted Living", "DAHS", "DAHS-ISS", "DAHS-ISSONLY"}
)

I created an insight parameter that includes this measure.
I have a bar chart that I am using the insight parameter and field parameter. The problem is when I select "LOF" from the insight parameter and then "program" in the field parameter, it gives me the total count of the LOF measure for each of the programs, instead of giving me indivudal values for the selected programs. It is also giving me a "blank" bar.

Is there a way to use the LOF measure with the field parameter?  The expected result would include values for each of the filtered programs, and 0 for the programs not included in the measure, and no blank bar.

For example if there are a total of 4 complaints for LOF, then I would like the chart to break down the total by the individual programs:
Assisted Living =1, DAHS=1, DAHS-ISS =1, DAHS-ISSONLY =1, and 0 values for the programs not included in the measure.

Thank you in advance for your help.

 

Need Help
  • Txtcher's avatar
    Txtcher
    1 year ago

    Thank you for response Anonymous .

     

    I logged on this morning to post that I have solved part of the problem, but now have encountered a new problem.

    This how I solved the bar chart issue:

    1. I created this measure:

    LOF_Backlog Insight = 
VAR SelectedParameter = SELECTEDVALUE(_LTCR_Dimension_par[Reg-Prog-Priority_par Order])
Var SelectedInsight = SELECTEDVALUE(_LTCR_Insights_par[_LTCR_Insights_par Order])
//Determine the parameter values selected. If LOF Insight and Program dimension
RETURN
SWITCH(
    TRUE(),
    SelectedParameter = 1 && SelectedInsight = 2,[LOF Backlog by Program],
    [LOF Backlog]
)

    I put the measure above into the insights parameter and the bar chart works perfectly now. 

    However, now the card visual returns a 0 when selecting both the LOF Backlog and Program parameter values. 

    This a screen shot of the bar chart & card visual (upper left corner). The card visual should not display 0.

    I would willingly share data, but I don't know what data to share that could help solve this issue. 

    In the meantime, I will mark this problem as solved as I did solve the original problem.

    Thank you again to Shahid12523  & FarhanJeelani  for their response.

     

5 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Icon for Super User rankSuper User
    1 year ago

    Hi Txtcher ,

    yes. You need a LOF Backlog measure that respects the current axis value (the field parameter) and returns 0 (not BLANK) for programs that aren’t in the LOF set.

     

    Why this happens

    Your LOF Backlog measure uses a fixed filter: CALCULATE([Backlog], 'RS Cases'[Program] IN {"Assisted Living","DAHS","DAHS-ISS","DAHS-ISSONLY"}).
    When you pick Field Parameter = Program, the visual shows one row per program, but the measure’s fixed filter doesn’t guarantee a per-program value in that context. For programs outside the LOF set you get BLANKs, and for the LOF programs you can end up seeing the total LOF across the four programs rather than the per-program breakdown.


    What to do

    Create a LOF Backlog measure that:
    Uses the current Program value from the visual (SELECTEDVALUE) to compute the per-program value
    Returns 0 for any program not in the LOF set (so you don’t get blanks)
    Example DAX (put this in place of your current LOF Backlog):

    LOF Backlog = VAR ThisProgram = SELECTEDVALUE ( 'RS Cases'[Program] ) VAR LOFSet = { "Assisted Living", "DAHS", "DAHS-ISS", "DAHS-ISSONLY" } RETURN IF ( ThisProgram IN LOFSet, CALCULATE ( [Backlog] ), 0 )


    Test example

    If total LOF backlog is 4 and the four LOF programs each have 1 backlog, you’ll get:
    Assisted Living = 1
    DAHS = 1
    DAHS-ISS = 1
    DAHS-ISSONLY = 1
    Any other Program value not in the LOF set = 0
    There should be no blank bars for the LOF programs, and the blank category should disappear if you remove items with no data or modify the axis options accordingly.

     

    Please mark this post as solution if it helps you. Appreciate Kudos.

  • Shahid12523's avatar
    Shahid12523
    Icon for Community Champion rankCommunity Champion
    1 year ago

    Your measure returns the same total for each program because it's not context-aware. To fix it, rewrite the measure like this:
    LOF Backlog by Program =
    VAR SelectedProgram = SELECTEDVALUE('RS Cases'[Program])
    RETURN
    IF(
    SelectedProgram IN {"Assisted Living", "DAHS", "DAHS-ISS", "DAHS-ISSONLY"},
    CALCULATE([Backlog], 'RS Cases'[Program] = SelectedProgram),
    0
    )


    This gives individual counts for each LOF program, zero for others, and removes the blank bar. Clean and accurate.

    • Txtcher's avatar
      Txtcher
      Icon for Helper V rankHelper V
      1 year ago

      Shahid12523 , FarhanJeelani 

       

      Thank you so much for your response. Your solution works but only if I select the Program field parameter.  If I select another parameter, it returns nothing. 
      What do I need to add to the measure for it to work with the other field parameters?

       

           

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Txtcher,

        Thank you  for reaching out to the Microsoft fabric community forum.

        I would also take a moment to thank Shahid12523, FarhanJeelani for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
        Also,  can you share a sample pbix file so that we can try to reproduce the issue from our side.

        Regards,
        Harshitha.

  • Txtcher's avatar
    Txtcher
    Icon for Helper V rankHelper V
    1 year ago

    Thank you for response Anonymous .

     

    I logged on this morning to post that I have solved part of the problem, but now have encountered a new problem.

    This how I solved the bar chart issue:

    1. I created this measure:

    LOF_Backlog Insight = 
VAR SelectedParameter = SELECTEDVALUE(_LTCR_Dimension_par[Reg-Prog-Priority_par Order])
Var SelectedInsight = SELECTEDVALUE(_LTCR_Insights_par[_LTCR_Insights_par Order])
//Determine the parameter values selected. If LOF Insight and Program dimension
RETURN
SWITCH(
    TRUE(),
    SelectedParameter = 1 && SelectedInsight = 2,[LOF Backlog by Program],
    [LOF Backlog]
)

    I put the measure above into the insights parameter and the bar chart works perfectly now. 

    However, now the card visual returns a 0 when selecting both the LOF Backlog and Program parameter values. 

    This a screen shot of the bar chart & card visual (upper left corner). The card visual should not display 0.

    I would willingly share data, but I don't know what data to share that could help solve this issue. 

    In the meantime, I will mark this problem as solved as I did solve the original problem.

    Thank you again to Shahid12523  & FarhanJeelani  for their response.