Forum Discussion

tvalente's avatar
tvalente
Icon for Helper I rankHelper I
4 years ago
Solved

A dynamic "Before" vs "After" label

Hi All,

 

I want to show a report that shows a survey, that has a baseline count let's call this "Before Trial".

 

Then I want to compare this to future surveys that will be all called "After Trial".

 

My problem is, I want to filter out the many after trials to a single "after trial" and compare that to a single "before trial".

 

The issue is if I filter on the "after trial" date, I will of course filter out the before trial.

 

My calculated dax formula attempts to create a Period Select filter, it fails. Because it satisfies the first condition, and then period 2 does not include the baseline period.

 

Period Select =
SWITCH ( TRUE,

DATEVALUE ( [date] ) IN { DATE ( 2020, 12, 03 ), DATE ( 2021, 08, 26 ) }, "Period 1"
DATEVALUE ( [date] ) IN { DATE ( 2020, 12, 03 ), DATE ( 2021, 09, 26 ) }, "Period 2"


)

 

I only managed to solve this by continually "unioning" the baseline data on each after trial survey (A very non elegant solution but it works).

 

PROBLEM

 

DatePeriod
29/09/2021Before Trial
30/09/2021After Trial
1/10/2021After Trial

 

SOLUTION (NON ELEGANT)

 

As below, I have continuously unioned the same survey 29/09/2021 to encapsulate it under a "Period 1" and "Period 2".

 

The slicer of "Date Selector" now works. But this is a very non elegant solution.

 

DatePeriodDate Selector
29/09/2021Before TrialPeriod 1
30/09/2021After TrialPeriod 1
29/09/2021Before TrialPeriod 2
1/10/2021After TrialPeriod 2

 

I don't know how to attach powerBI workbooks for a sample workbook. So please visit here:

 

https://easyupload.io/fe5n6y

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    tvalente One way is to create a disconnected slicer table that is simply an Enter data query with 2 rows

    Period 1

    Period 2

     

    Mine is called Table and the column is Period Select. Make sure this is not connected to anything (relationships). Use this as your slicer. Then create a measure:

     

     

    Measure = 
    VAR __PeriodSelect = SELECTEDVALUE('Table'[Period Select])
    VAR __Period = MAX('Problem'[Period])
RETURN
    IF(
        __Period = "After Trial",
        SUMX(FILTER(ALLSELECTED('Problem'),[Period]=__Period && [Period Select] = __PeriodSelect),[Count of Dogs]),
        SUMX(FILTER(ALL('Problem'),[Period]=__Period),[Count of Dogs])
    )

     

     

    Use this measure in the value for your visual. I have attached your PBIX but it updated and you are using RS version so you may not be able to open it.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    tvalente One way is to create a disconnected slicer table that is simply an Enter data query with 2 rows

    Period 1

    Period 2

     

    Mine is called Table and the column is Period Select. Make sure this is not connected to anything (relationships). Use this as your slicer. Then create a measure:

     

     

    Measure = 
    VAR __PeriodSelect = SELECTEDVALUE('Table'[Period Select])
    VAR __Period = MAX('Problem'[Period])
RETURN
    IF(
        __Period = "After Trial",
        SUMX(FILTER(ALLSELECTED('Problem'),[Period]=__Period && [Period Select] = __PeriodSelect),[Count of Dogs]),
        SUMX(FILTER(ALL('Problem'),[Period]=__Period),[Count of Dogs])
    )

     

     

    Use this measure in the value for your visual. I have attached your PBIX but it updated and you are using RS version so you may not be able to open it.