Dynamic measure that changes based on field selected by the user

[Anonymous]

Hey guys,

I would like to ask if it is possible to create a measure that adapts to the user selection. 

To elaborate further, let's say  I have  4 date fields ( Created Date,  Activated Date, Resolved Date and Closed Date) and an ID. What I need is the option to the user to select from two slicers the Date fields he/she wants to compare.

Thus, if I select Created Date and Closed Date, the measure will be DATEDIFF(Created Date,Closed Date,DAY), if  I select Activated Date and Closed Date, the measure will be DATEDIFF(Activated Date,Closed Date,DAY) etc. This must work for every date combination. This is intended to be used in a table visual (I really hope that is possible)!!

Thanks in advance for your time and effort!!

[v-luwang-msft]

Hi @Anonymous ,

Test the below:

test = 
VAR test1 =
    CONCATENATEX ( VALUES ( Slicer[Slicer] ), [Slicer], "," )
VAR q1 =
    IF (
        ISERROR ( FIND ( "Created Date", test1 ) ),
        BLANK (),
        FIND ( "Created Date", test1 )
    )
VAR q2 =
    IF (
        ISERROR ( FIND ( "Closed Date", test1 ) ),
        BLANK (),
        FIND ( "Closed Date", test1 )
    )
VAR q3 =
    IF (
        ISERROR ( FIND ( "Activated Date", test1 ) ),
        BLANK (),
        FIND ( "Activated Date", test1 )
    )
RETURN
    IF (
        Q1 >= 1
            && Q2 >= 1,
        DATEDIFF ( MAX ( 'Table'[Created Date] ), MAX ( 'Table'[Closed Date] ), DAY ),
        IF (
            q2 >= 1
                && q3 >= 1,
            DATEDIFF ( MAX ( 'Table'[Activated Date] ), MAX ( 'Table'[Closed Date] ), DAY ),
            BLANK ()
        )
    )

Refer:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Anonymous]

Hi @v-luwang-msft ,

thanks for your reply.  I liked your solution but I think it is not robust enough in my case of course. The ultimate goal is to have 2 slicers with the same Date fields and depending which is selected first, will be the first component of the datediff function. 
Also there are 12 date fields in total, so something much hard coded will not be the optimal solution for my case. 

Should you have any ideas, plese let me know. Thanks once again!!!🤗
 

[v-luwang-msft · Accepted solution]

HI @Anonymous ,

Base on my before pbix file ,try the below operations:

Step1,copy the base table ,and unpovit：

Step2, base on the below we have ,create the below measure:

testq = 
VAR test1 =
    CALCULATE (
        MAX ( Table2[Value] ),
        FILTER (
            ALL ( Table2 ),
            Table2[ID] = MAX ( Table2[ID] )
                && Table2[Attribute] = SELECTEDVALUE ( Slicer[Slicer] )
        )
    )
VAR test2 =
    CALCULATE (
        MAX ( Table2[Value] ),
        FILTER (
            ALL ( Table2 ),
            Table2[ID] = MAX ( Table2[ID] )
                && Table2[Attribute] = SELECTEDVALUE ( Table2[Attribute] )
        )
    )
RETURN
    ABS ( DATEDIFF ( test1, test2, DAY ) )

Then create the below measure:

 Relationship:

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[NickolajJessen]

So, one idea comes to mind.

You can create 2 disconnected lookup tables, that both contain CreatedDate, Activated Date, Resolved Date and Closed Date.

Next you go and create measure, that calcualtes based on the selection made in those 2 disconnected lookup talbes.

Measure = 

Var _SelectedDateChoice1 = 
  Switch( 
  Selectedvalue('DisconnectedLookupTable1'[DateChoice]),
  "CreatedDate", Max('Table1'[CreatedDate]),
  "ActivatedDate", Max('Table1'[ActivatedDate])
)

Var _SelectedDateChoice2 =
  Switch( 
  Selectedvalue('DisconnectedLookupTable2'[DateChoice]),
  "ClosedDate", Max('Table1'[ClosedDate]),
  "ResolvedDate", Max('Table1'[ResolvedDate])
)
Return
_SelectedChoice2 - _SelectedChoice1 '' 

[Anonymous]

Hi @NickolajJessen. 

I don;t think I understand the solution you propose.

Could you elaborate a bit more?