Use measure in FILTER function

[LauraBueno]

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 calculated measure is not working.

Could anyone please help me to see how I could use the measure within the filter?

I cannot direclty use the scalar since the value I need to use is dynamically changing based on a slicer.

Thanks a lot in advance

[V-lianl-msft]

Hi @LauraBueno ,

Please refer to this thread:https://community.powerbi.com/t5/Desktop/Filtering-using-a-measure-insidea-calculate-function/m-p/84... 

If the problem persists,could you please share sample data and the result you expected?

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LauraBueno]

Hi @V-lianl-msft ,

Thank you for your message. I did try the solution described in the post you shared. However, it is not completely working for me. 

The picture below shows the sample data I have:

There are several studies and each study with multiple simulations. The variable I am intersted in is 'value' which also depends on the column called 'type'.

What I am trying to do is, 1) Select study via slicer in dashboard (which is link to simulations and hence the slicer for simulations automatically change), 2) Select reference simulation via a different slicer. 3) For the simulation selected in the slicer, I need to do the following calculation:

Value (for all simulations for a fiven study) - Value of simulation selected in slicer filtered by 'Type'.

Could you please help me with this? Thank you so much in advance!

[MartynRamsden]

Hi @LauraBueno 

Can you provide the full DAX expression in which you're using the FILTER function?

Is it a measure or a calculated column?

Best regards,

Martyn

[LauraBueno]

Hi @MartynRamsden,

Thank you for your reply. Please see below the full expression where I'm using FILTER function:

Ref_table = FILTER('db_datareader S','db_datareader S'[Simulation] = 'db_datareader Simulation'[Reference_Sim_Id])

I'm trying to create a subtable by filtering the datatable 'db_datareader S' based on its column 'Simulation', so that the new table only contains the rows from the original table where Simulation column equals the value given by the measure  'db_datareader Simulation'[Reference_Sim_Id].

If instead of  'db_datareader Simulation'[Reference_Sim_Id], I use directly the value from the measure (i.e. 1714), then it works.

I'm wondering how I could use the measure  'db_datareader Simulation'[Reference_Sim_Id] directly in that expression?

Thanks

Laura

[MartynRamsden]

Hi @LauraBueno 

If I understand correctly, you're trying to create a calculated table 'on the fly'? If so, this isn't supported.

Calculated tables (and columns) are only computed when the data is refreshed.

What's your ultimate result? There may be an alternative method to achieve what you're looking for.

Best regards,

Martyn

[LauraBueno]

Hi @MartynRamsden,

I have a table that looks similar to the one below where I have a study that contains multiple simulations. The variable 'V1' is the output which is measured across different sample types ('column type').

I need to do a simple calculation as follows: V1_i - Vref where V1_i would be the values of all the simulations for a given study a sample type and Vref is the reference simulation selected via a slicer.

So in order to do that I need to be able to put filters by study, simulation and type of sample.

Any ideas how I could do this within this table?

Study	Simulation	Type	V1
1	1	1	23
1	2	1	25
1	3	2	65
1	4	2	72
1	5	3	98
1	6	4	12
1	7	5	5
1	8	6	36
1	9	7	66
1	10	8	35

Thanks

Laura

[MartynRamsden]

Hi @LauraBueno 

Are you able to show me an example of your expected output using your sample data?

Best regards,

Martyn

[LauraBueno]

Hi @MartynRamsden,

The picture below shows the sample data I have:

There are several studies and each study with multiple simulations. The variable I am intersted in is 'value' which also depends on the column called 'type'.

What I am trying to do is, 1) Select study via slicer in dashboard (which is link to simulations and hence the slicer for simulations automatically change), 2) Select reference simulation via a different slicer. 3) For the simulation selected in the slicer, I need to do the following calculation:

Value (for all simulations for a fiven study) - Value of simulation selected in slicer filtered by 'Type'.

Could you please help me with this? Thank you so much in advance!

[LauraBueno]

@MartynRamsden 

[MartynRamsden]

Hi @LauraBueno 

Try something like this:

Variance Value = 
VAR SelValue = SUM ( Table1[Value] )
VAR TotalStudyValue = 
CALCULATE ( 
    SUM ( Table1[Value] ),
    ALL ( Table1[Simulation] )
)
VAR Result = TotalStudyValue - SelValue
RETURN Result

You'll need to replace the table and column names with those in your actual model.

Best regards,

Martyn

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

[LauraBueno]

Hi @MartynRamsden ,

Thank you for your message. However, I need to calculate the difference of Value (this is the value vairable for all the simulations within the study) - Value_ref (this is the value variable for the simulation selected via the slicer) for each individual simulation filtered bt the type column (not the difference of the sum ).

[MartynRamsden]

Hi @LauraBueno

Are you able to show me a worked example and your expected output?

I'm struggling to understand what you want to calculate.

Best regards,

Martyn

[LauraBueno]

Hi @MartynRamsden :

Please see below an example of expected output:

1) Select reference simulation via slicer (i.e. 2498)

2) Next using that simulation as reference, I need to do the following calculation: Value - Value_ref which is the difference of the variable value for all the simulations minus the variable value for reference simulation filtered by type.

Hence the output would be as follows:

Delta is the new calculated column (row 1 = 44.1385 - 38.695, row 2 = 41.7265 - 38.695, row3 = 41.614 - 38.695...)

Simulation	Study	Type	Value	delta
2498	39	27	38.695	5.4435
2499	39	27	44.1385	3.0315
2500	39	27	41.7265	2.919
2501	39	27	41.614	-11.349
2502	39	27	27.346	1.7455
2503	39	27	40.4405	-0.6745
2504	39	27	38.0205	0.0315
2505	39	27	38.7265	-8.7845
2506	39	27	29.9105	2.364
2507	39	27	41.059	5.8525
2508	39	27	44.5475	2.2335
2509	39	27	40.9285	-38.695
...

Thanks

Laura

[MartynRamsden · Accepted solution]

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

[LauraBueno]

Hi @MartynRamsden

Thanks a lot for your reply! It worked very well! 🙂 

I have a slightly different problem now though 😞 aybe you could help me with?

Once I have done the calculation of the deltas I would like to create a Radar plot that shows the delta value for every different variable type. However, when trying to do that, the value that is plotted in the Radar plot is the some across all the simulations in a study. I was wondering if there is anyway to show the individual value for each simulation within a study?

Thank you so much in advance!

Best regards

Laura

[MartynRamsden]

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]

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]

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]

Hi @MartynRamsden ,

No, not yet 😞 

I was hoping you could help 🙂

Best regards

Laura

[MartynRamsden]

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.