cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
SteelerFan
Frequent Visitor

Max Effective Term by Category based on Slicer Selection - Only return Max Effective Term Row

Hi,

 

I'm trying to return a single row for the max effective term for the selected Term, Subject and Course.  My table called SSBSECT has multiple rows per term, but has a row for every term.  My table SCBCRSE may or may not have a row for every Subject and Course, so it has an effective term field.

 

My Tables are SSBSECT and SCBCRSE and are joined together in the data model by subject/crse.

 

I need to be able to select the Term and it retrieve the max effective term row only from the SCBCRSE table for the subject and course, however the max effective term row must be <= the selected term because the exact term may not be in the SCBCRSE table.  I will need to select other fields from the max term row returned from SCBCRSE to be displayed on my visual.  So, I get one row returned for the selected Term / Subj / CRSE from the SCBCRSE and any additional information for that row from the SCBCRSE table. 

 

This is a screenshot of my data, when I select Term 202305 it should return the row from SCBCRSE with the SCBCRSE_EFF_TERM field = to 201608.  If I changed the term to 201308, it should select the row with the effective term of 201208.  Thus returning a single row from the SCBCRSE table with the max effective term <= the prompted term.

 

My current measures are as follows:

 

-- Get the Max Effective Term from SCBCRSE

SCBCRSE Max Effective Term =

    MAX(scbcrse[scbcrse_eff_term])

 

 

-- Returns the Max effected term for the selected subject /crse.  This appears to be working correctly when changing terms from the slicer.

Max Eff Term by CRSE_NUMB =

    CALCULATE(

        [SCBCRSE Max Effective Term], ALLEXCEPT(scbcrse, scbcrse[scbcrse_subj_code], scbcrse[scbcrse_crse_numb]),

         FILTER(

        ALL(scbcrse),

        scbcrse[scbcrse_eff_term] <= MAX(ssbsect[ssbsect_term_code])

    )

    )

For term 202305, should display the highlighted rows.

 

SteelerFan_0-1695654913302.png

 

 

Changing the term to 201305 should display the highlighted rows.

SteelerFan_1-1695654913304.png

 

 

Any help is greatly appreciated.

 

Thanks!!

2 REPLIES 2

Hi @amitchandak ,  can't get it to work, but I have to admit, I'm a newbie, so trying to understand the row context vs filter context from a PBI perspective.  In my example above, this is how I solve it with SQL.  So any help in translating that to Power bi would be much appreciated.  The kicker is the term needs to be dynamic, in SQL I would have it as a bind variable (:term)

 

select DISTINCT SCBCRSE.SCBCRSE_EFF_TERM ,
       SCBCRSE.SCBCRSE_SUBJ_CODE ,
       SCBCRSE.SCBCRSE_CRSE_NUMB ,
       SCBCRSE.SCBCRSE_COLL_CODE ,
       SCBCRSE.SCBCRSE_DEPT_CODE ,
       SCBCRSE.SCBCRSE_TITLE ,
       SCBCRSE.SCBCRSE_CSTA_CODE,
       a.ssbsect_subj_code,
       a.ssbsect_crse_numb
  from SCBCRSE SCBCRSE
  join ssbsect a on a.ssbsect_subj_code = SCBCRSE.scbcrse_subj_code
                and a.ssbsect_crse_numb = SCBCRSE.scbcrse_crse_numb
 --               and ssbsect_term_code = '201305'
 
 where SCBCRSE.SCBCRSE_EFF_TERM = (SELECT MAX(X.SCBCRSE_EFF_TERM)
                      FROM scbcrse x
                      WHERE x.scbcrse_subj_code = scbcrse.scbcrse_subj_code
                      AND x.scbcrse_crse_numb = scbcrse.scbcrse_crse_numb
                     and x.scbcrse_eff_term <= '201305')
 --                     AND x.scbcrse_eff_term <= :dd_term.CODE)

       and SCBCRSE.SCBCRSE_SUBJ_CODE = 'ZZZ'
 --      and ( SCBCRSE.SCBCRSE_SUBJ_CODE = :lb_subj.CODE
      and SCBCRSE.scbcrse_crse_numb = '634'

 order by SCBCRSE.SCBCRSE_SUBJ_CODE,
          SCBCRSE.SCBCRSE_CRSE_NUMB

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors