Forum Discussion

tcheong's avatar
tcheong
Frequent Visitor
5 years ago
Solved

Group by different column based on slicer value

Hello, please refer to this pbix for this question.

 

Background:

I have the following data in the 'Products' table:

IdCreatedDateGradeModifiedDateNewGrade
1

10 Nov 2020

A10 Nov 2020B
212 Nov 2020A16 Nov 2020C
315 Nov 2020B19 Nov 2020C
422 Nov 2020B30 Nov 2020A

 

The values of [Grade] and [NewGrade] are always either "A", "B", or "C". 

 

There is also a 'DateDim' table with a one-to-many relationship to 'Products'.[CreatedDate]. A date range slicer is created for 'DateDim'.[Date]:

 

The Problem:

I want to know how many products are in each grade within the selected date range. However, if the max date in the date range slicer is >= [ModifiedDate], it should be grouped by the value in [NewGrade] instead of [Grade].


For example, the expected result for a date range between 10/11/2020 and 17/11/2020 should be:

GradeCount
B2
C

1


Expected results for a date range between 10/11/2020 and 22/11/2020 should be:

GradeCount
B2
C

2

 

How can I achieve these results?

Need Help
  • tcheong's avatar
    tcheong
    5 years ago

    Hello, I managed to achieve what I needed by creating a measure for each grade like so:

    _ProductCount_GradeA = 
VAR slicerMaxDate = MAX(DateDim[Date])

RETURN
    CALCULATE(
        COUNT(Products[Id]),
        FILTER(
            ALL(Products[ModifiedDate], Products[Grade], Products[NewGrade]),
            IF(Products[ModifiedDate] <= slicerMaxDate, [NewGrade], [Grade]) = "A"
        )
    ) + 0

     

    You may refer to this PBIX for my solution.

     

    Thanks to everyone who took the time to consider my question. Have a nice day!

1 Reply

  • tcheong's avatar
    tcheong
    Frequent Visitor
    5 years ago

    Hello, I managed to achieve what I needed by creating a measure for each grade like so:

    _ProductCount_GradeA = 
VAR slicerMaxDate = MAX(DateDim[Date])

RETURN
    CALCULATE(
        COUNT(Products[Id]),
        FILTER(
            ALL(Products[ModifiedDate], Products[Grade], Products[NewGrade]),
            IF(Products[ModifiedDate] <= slicerMaxDate, [NewGrade], [Grade]) = "A"
        )
    ) + 0

     

    You may refer to this PBIX for my solution.

     

    Thanks to everyone who took the time to consider my question. Have a nice day!