Forum Discussion

LauraBueno's avatar
LauraBueno
Helper III
6 years ago
Solved

Use measure in FILTER function

Hi,   I'm trying to use the FILTER function to create a subtable based on the value of one of the columns. when I input a scalar value directly is working, however, when using the vlaue from a calc...
Need Help
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    Hi LauraBueno 

     

    You'll need to create a disconnected slicer - this is a slicer that won't filter other visuals in your report.

     

    To do this, you need create a new table using the following DAX expression:

    DisconnectedTable = ALL ( 'db_datareader Simulation'[Study], 'db_datareader Simulation'[Simulation] )

     

    Then create the following measures:

    Total Value = SUM ( 'db_datareader Simulation'[Value] )
    Delta = 
VAR Sim_Ref = SELECTEDVALUE ( DisconnectedTable[Simulation] )
VAR Value_Ref = 
CALCULATE ( 
    SUM ( 'db_datareader Simulation'[Value] ),
    'db_datareader Simulation'[Simulation] = Sim_Ref
)
VAR SumVal = [Total Value]
VAR Result = SumVal - Value_Ref
RETURN Result

     

    In your report, add two slicers:

    1. 'db_datareader Simulation'[Study]
    2. 'DisconnectedTable'[Simulation]

     

    Then add a table visualisation with the following columns from the 'db_datareader Simulation' table:

    • Study
    • Simulation
    • Type
    • Value
    • [Delta] measure

     

    You'll find that your Simulation slicer won't automatically filter depending on your selection in the Study slicer.

    If you want this to work, you can create the measure below and add it to the Simulation slicer as a visual filter and set the value to 1.

     

    SlicerFilter = 
VAR SelStudy = SELECTEDVALUE ( 'db_datareader Simulation'[Study] )
VAR SimStudy = SELECTEDVALUE ( DisconnectedTable[Study] )
VAR Result = 
IF ( 
    SimStudy = SelStudy,
    1,
    0
)
RETURN Result

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    This is the result I got using the data you provided:

     

     

     

     

     

     

     

     

     

     

     

     

     

    Fingers crossed, this will work for you!

     

    Best regards,

    Martyn

MartynRamsden's avatar
MartynRamsden
Solution Sage
6 years ago

Hi LauraBueno 

 

Glad the previous solution worked for you!

 

I've done a quick test using your sample data and it looks like the radar chart is working as expected.

 

When simulation 2498 is selected in the slicer, the Delta for simulation 2499 is 5.44 - this is displayed correctly on the chart.

 

 

 

When simulation 2500 is selected in the slicer, the Delta for simulation 2499 is 2.41 - again, this is displayed correctly on the chart.

 

 

Not sure if the confusion is coming from the fact that the line on the radar chart doesn't move? If so, it's because the chart is automatically scaled so the minimum value is in the centre.

You can see the 'Axis shift' value under the formatting options on the Visualizations pane (Display settings>> Axis shift).

 

Hope it helps.

 

Best regards,

Martyn

 

If I answered your question, please help others by accepting it as a solution.

LauraBueno's avatar
LauraBueno
Helper III
6 years ago

Hi MartynRamsden ,

 

Thanks again for your reply!

 

I think the issue I have with the radar chart is due to the column named 'Type'. In the example data I shared before (please see below), the 'Type' column has a constant value across all rows.

 

 

However, in the full set of data, I have several values within this Type column. Hence, when I try to do the radar chart I'm adding the column 'Type' as category so that I can visualise what is the delta value for each 'Type' and the calculated 'delta' within the Y-Axis.

 

When I do that the radar chart shows the sum of all the delta values per type. I was wondering whether there is a way to show the delta for each individual simulation within the study per 'type'?

 

 

Many Thanks in advance!

 

Best regards

Laura

 
  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Hi LauraBueno 

     

    Did you manage to find a solution for this?

     

    Best regards,
    Martyn


    If I answered your question, please help others by accepting it as a solution.

  • LauraBueno's avatar
    LauraBueno
    Helper III
    6 years ago

    Hi MartynRamsden ,

     

    No, not yet 😞 

     

    I was hoping you could help 🙂

     

    Best regards

    Laura

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Hi LauraBueno 

     

    I don't think the Radar Chart visualisation will support what you're trying to achieve with the data model you currently have.

     

    One option is to create a separate measure to calculate the delta for each category but this is far from ideal and would be a pain to manage.

    I can see a possible solution with the use of a calculated table but  I'll need to investigate this further. I will come back to you, although it may be tomorrow!

     

    A final option is to display the results in a line chart instead. Would this be acceptable?

     

    Best regards,
    Martyn


    If I answered your question, please help others by accepting it as a solution.

     

  • LauraBueno's avatar
    LauraBueno
    Helper III
    6 years ago

    Hi MartynRamsden ,

     

    Thank you for your message. If possible I would prefer to create a radar chart vs a line one 🙂 so if there is an option to create a calculated table that allows to build the radar chart that would be perfect. 

     

    Many thanks for your help,

     

    Best regards,

    Laura

  • LauraBueno's avatar
    LauraBueno
    Helper III
    6 years ago

    Hi MartynRamsden ,

     

    Any ideas on how to deal with the radar chart? 😊

     

    Many thanks in advance!

     

    best regards

    Laura

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Hi LauraBueno 

    Once again, apologies for the delayed reply.

     

    I've tried various approaches and the only that works is to create a separate measure for each simulation.

    The measures follow this pattern:

    Delta Sim 2498 = 
VAR Value_Ref = 
CALCULATE ( 
    SUM ( 'db_datareader Simulation'[Value] ),
    'db_datareader Simulation'[Simulation] = "2498"
)
VAR SumVal = [Total Value]
VAR Result = SumVal - Value_Ref
RETURN Result

     

    You then add each of these measures to the Y-Axis of the Radar Chart and it produces something like this:

     

     

    Obviously, there's a lot of overhead producing all of these measures so, if it were me, I'd opt for a line chart (I'd even argue that the data is better displayed in this way, but that's just me).

    You can simply add your Type column to the Axis, your Simulation column to the Legend and the Delta measure to the Values and it would produce this:

     

     

    I hope this gives you some inspiration - good luck!

     

    Best regards,
    Martyn


    If I answered your question, please help others by accepting it as a solution.