Forum Discussion

patilpoonam21's avatar
patilpoonam21
Icon for Helper I rankHelper I
2 years ago
Solved

Calculate Last 2 Quarters without Date field

Hi,

 

I need to check if last 2 Quarters Att is less than 100%, then need to show some flag.

 

Below is the data:

 

Emp_IdPersonQuarterCreditQuotaAtt
1AQ110200.5
1AQ211200.55
1AQ345202.25
1AQ413200.65
2BQ170601.166667
2BQ220600.333333
2BQ325600.416667
2BQ430600.5
3CQ130700.428571
3CQ272701.028571
3CQ373701.042857
3CQ426700.371429

 

I need to provide one slicer:

 

Quarter
Q1 & Q2
Q2 & Q3
Q3 & Q4

 

If user selects, Q1 & Q2, need to check if both of the Quarter's Att is less than 1 then, I need to show one column with the red flag for that row.

In the above scenario, the red flag should be for person 'A'.

Can anyone please help on that?

TIA.

 

- Rinku

Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    patilpoonam21 , One way is to have the table you have shown below as disconnected table and use

    measures like

    Combined Attendance =
    VAR _sel = SELECTEDVALUE('QuarterCombination'[QuarterCombination])
    VAR EmpID = MAX('Data'[Emp_Id])
    RETURN
    SUMX(
    FILTER(
    'Data',
    'Data'[Emp_Id] = EmpID &&
    (
    (_sel = "Q1 & Q2" && 'Data'[Quarter] IN {"Q1", "Q2"}) ||
    (_sel = "Q2 & Q3" && 'Data'[Quarter] IN {"Q2", "Q3"}) ||
    (_sel = "Q3 & Q4" && 'Data'[Quarter] IN {"Q3", "Q4"})
    )
    ),
    'Data'[Att]
    )

    Attendance Flag =
    IF([Combined Attendance] < 1, "Red Flag", "No Flag")

     

    Another is have table like this

    Quarter , Qtr Key
    Q1 & Q2, Q1
    Q1 & Q2, Q2
    Q2 & Q3, Q2
    Q2 & Q3, Q3
    Q3 & Q4, Q3
    Q3 & Q4, Q4


    Many to Many bi- directional join and then you can create measures without working on filter

     

    example

    Combined Attendance =
    VAR EmpID = MAX('Data'[Emp_Id])
    RETURN
    SUMX(
    FILTER(
    'Data',
    'Data'[Emp_Id] = EmpID
    ),
    'Data'[Att]
    )

    example

    Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c
    https://medium.com/@amitchandak/power-bi-search-text-parts-in-text-ad2f9783ffa2

     

  • Daniel29195's avatar
    Daniel29195
    2 years ago

    Hello  patilpoonam21

    the output : 

     

    i will help you out with you request . 

    step 1 :  create manually the following table : 

     

    step 2 :  

    link this table to your table on quarter column .

     

     NB :  eventhough it is a many ro many relationship, however since you are selecting w group , then the relationship will work as 1 - to many  . 

     

     

    step 3 :  create this measure : 

    Measure 15 =

    var test =
    ADDCOLUMNS(
        CALCULATETABLE(
        'Table (19)',
        ALLSELECTED('Table (20)'[Quarter])
        ),
        "check" ,
        SWITCH(
            TRUE(),
            'Table (19)'[Att] < 1 , 1, 0 )
    )

    var result =
    SUMX(
        test,
        [check]
    )

    RETURN  
    SWITCH(
        TRUE(),
        result < 2 ,BLANK() , "FlagLow"
    )



    step 4 : create the visual 

    NB  : the quarter column should read from the new created table having the groups . 

     

     

    step 5 : right click on att  column -->  conditional formatting --> icon

     

     

     

    step 6 : choose field value , and in the second dropdown, choose the measure created earlier . click ok .

     

     

     

    the output : 

     

     

     

     

    let me know if this works for you . 

     

    best regards

9 Replies

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

    patilpoonam21 , One way is to have the table you have shown below as disconnected table and use

    measures like

    Combined Attendance =
    VAR _sel = SELECTEDVALUE('QuarterCombination'[QuarterCombination])
    VAR EmpID = MAX('Data'[Emp_Id])
    RETURN
    SUMX(
    FILTER(
    'Data',
    'Data'[Emp_Id] = EmpID &&
    (
    (_sel = "Q1 & Q2" && 'Data'[Quarter] IN {"Q1", "Q2"}) ||
    (_sel = "Q2 & Q3" && 'Data'[Quarter] IN {"Q2", "Q3"}) ||
    (_sel = "Q3 & Q4" && 'Data'[Quarter] IN {"Q3", "Q4"})
    )
    ),
    'Data'[Att]
    )

    Attendance Flag =
    IF([Combined Attendance] < 1, "Red Flag", "No Flag")

     

    Another is have table like this

    Quarter , Qtr Key
    Q1 & Q2, Q1
    Q1 & Q2, Q2
    Q2 & Q3, Q2
    Q2 & Q3, Q3
    Q3 & Q4, Q3
    Q3 & Q4, Q4


    Many to Many bi- directional join and then you can create measures without working on filter

     

    example

    Combined Attendance =
    VAR EmpID = MAX('Data'[Emp_Id])
    RETURN
    SUMX(
    FILTER(
    'Data',
    'Data'[Emp_Id] = EmpID
    ),
    'Data'[Att]
    )

    example

    Power BI- Text Part slicer to filter/search text - https://youtu.be/MKKWeOqFG4c
    https://medium.com/@amitchandak/power-bi-search-text-parts-in-text-ad2f9783ffa2

     

    • patilpoonam21's avatar
      patilpoonam21
      Icon for Helper I rankHelper I
      2 years ago

      Hi amitchandak ,

       

      Thank you so much for your reply. 

      I tried the first approach and it's working fine but I need to show "Red flag" if the value is '<1' for both the quarter.

      For the above table, if I select "Q1 & Q2" then all three person, A,B & C will have the "Red flag" for Q1 and Q2. Currently I am getting "Red flag" for the individual row.

      Sorry if I confused you.

       

      Thanks,

      Poonam.

  • Daniel29195's avatar
    Daniel29195
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hello  patilpoonam21

    the output : 

     

    i will help you out with you request . 

    step 1 :  create manually the following table : 

     

    step 2 :  

    link this table to your table on quarter column .

     

     NB :  eventhough it is a many ro many relationship, however since you are selecting w group , then the relationship will work as 1 - to many  . 

     

     

    step 3 :  create this measure : 

    Measure 15 =

    var test =
    ADDCOLUMNS(
        CALCULATETABLE(
        'Table (19)',
        ALLSELECTED('Table (20)'[Quarter])
        ),
        "check" ,
        SWITCH(
            TRUE(),
            'Table (19)'[Att] < 1 , 1, 0 )
    )

    var result =
    SUMX(
        test,
        [check]
    )

    RETURN  
    SWITCH(
        TRUE(),
        result < 2 ,BLANK() , "FlagLow"
    )



    step 4 : create the visual 

    NB  : the quarter column should read from the new created table having the groups . 

     

     

    step 5 : right click on att  column -->  conditional formatting --> icon

     

     

     

    step 6 : choose field value , and in the second dropdown, choose the measure created earlier . click ok .

     

     

     

    the output : 

     

     

     

     

    let me know if this works for you . 

     

    best regards

    • patilpoonam21's avatar
      patilpoonam21
      Icon for Helper I rankHelper I
      2 years ago

      Hi Daniel29195 ,

       

      Thank you so much for your reply. Really appreciate the detailed instructions.

      We are using Power BI Desktop 2019 version. I am not able to create many to many relationship there.

      I created one bridge table, "Quarter" and joined the "Table 19" and "Table 20".
      Also, when I right click to create a visual, I don't get "Icon" option. I tried with Background color option but its not working. Am I missing anything?

       

       

      Measure 15 =

      var test =
      ADDCOLUMNS(
      CALCULATETABLE(
      'Sheet2',
      ALLSELECTED('Quarter Bridge'[Quarter])
      ),
      "check" ,
      SWITCH(
      TRUE(),
      'Sheet2'[Att] < 1 , 1, 0 )
      )

      var result =
      SUMX(
      test,
      [check]
      )

      RETURN
      SWITCH(
      TRUE(),
      result < 2 ,BLANK() , "FlagLow"
      )
      • patilpoonam21's avatar
        patilpoonam21
        Icon for Helper I rankHelper I
        2 years ago

        Hi Daniel29195 ,
        The above solution is working when I created bidirectional relatioship for both tables with the sample data. 

         

         


        In my scenarion, the "Att" column is calculated column.
        I am using below expression to use that column, as I have 'Month' in my table. I just wanted to avoid it.

        Att = CALCULATE (
        SUM(MonthlyAnalysisCRQT[Credit]),
        ALLEXCEPT(MonthlyAnalysisCRQT, MonthlyAnalysisCRQT[LI_EMP_ID],MonthlyAnalysisCRQT[Fiscal Year],MonthlyAnalysisCRQT[Quarter])
        )/
        CALCULATE (
        SUM(MonthlyAnalysisCRQT[Quota]),
        ALLEXCEPT(MonthlyAnalysisCRQT, MonthlyAnalysisCRQT[LI_EMP_ID],MonthlyAnalysisCRQT[Fiscal Year],MonthlyAnalysisCRQT[Quarter])
        )

        I am using that column to calculate last 2 Quarter flag but its not working.
        Flag =
        var test =
        ADDCOLUMNS(
        CALCULATETABLE(
        MonthlyAnalysisCRQT, ALLSELECTED('Quarter Bridge'[Quarter])
        ),
        "check" ,
        SWITCH(
        TRUE(),
        MonthlyAnalysisCRQT[Att]
        < 1 , 1, 0 )
        )
         

        var result =
        SUMx(test, [check]
        )

        RETURN
        SWITCH(
        TRUE(),
        result < 2 ,BLANK() , "#CA3433"
        )
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Do you have a proper Date column?  Would you be OK if the user selects only a single quarter in the slicer - the measure can be adapted to look at the chosen quarter and the next quarter.

    If you have a date column and are amenable to my suggestion of selecting a single quarter in the slicer, then show the expected result very clearly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi patilpoonam21,

    Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

    If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng