Forum Discussion

michaelccdf's avatar
michaelccdf
Icon for Helper I rankHelper I
6 years ago
Solved

Calculated field on dimension table from fact table

I have two tables, Attendance and Students.    Attendance Date Period CourseID StudentID IsAbsent 2020-01-01 1 Math 1 0 2020-01-01 1 Math 2 1 2020-01-01 2 Science 1 1 ...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi michaelccdf ,

     

    I know what you want, but the values of calculated columns do not change with different filters. They are fixed, but measures are not. So, creating Measures is the best option. 

    Please check if this could meet your requirements.

    1. Create Measures.

    AbsentPeriod = SUM(Attendance[IsAbsent])
    Absenteeism Level = IF ( [AbsentPeriod] >= 1, "High Absenteeism", "Low Absenteeism" )

     

    2. Put "Absenteeism Level" measure on the visuals you want to filter.

     

     

    Best Regards,
    Icey

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

Hi michaelccdf ,

 

Based on my understanding, it is not necessary to create calculated columns or measures in your scenario.

 

Try this:

 

Create a table visual like so:

 

Then, you can keep filters working.

 

 

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

  • michaelccdf's avatar
    michaelccdf
    Icon for Helper I rankHelper I
    6 years ago

    Thanks, this table visual is sort of what I am trying to get to but, I then I want to go a step further.  For example, I would like to group the students based on the SUM(IsAbsent) Column into High Absenteeism and Low Absenteeism students and then use this column to filter the Attendance table. 

     

    This situation is similar to having a sales and a products table: I would want to have a total sales column in the products table so that I could then group them into best selling, average selling and worst selling groups and then filter the sales table with these groups. 

    • Icey's avatar
      Icey
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi michaelccdf ,

       

      I know what you want, but the values of calculated columns do not change with different filters. They are fixed, but measures are not. So, creating Measures is the best option. 

      Please check if this could meet your requirements.

      1. Create Measures.

      AbsentPeriod = SUM(Attendance[IsAbsent])
      Absenteeism Level = IF ( [AbsentPeriod] >= 1, "High Absenteeism", "Low Absenteeism" )

       

      2. Put "Absenteeism Level" measure on the visuals you want to filter.

       

       

      Best Regards,
      Icey

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.