group lines when all selected

[buchta]

Hi 

I would like to create a measrue that will show the average price in specific way in line chart. 

I have DIM Container table with ISO Code and I have Fact Table with Price per day and Isocode.
I would like to put this measure in line chart with the following condition

if nothing is selected show one line as an total average price for all isocodes but when one or multiple isocodes are selected in slicer i want each of the isocodes to be displayed with individual line in the graph

i tried something like this 
Dynamic AVG Price=

var filtered = ISFILTERED('DIM Container'[ISO Code])

var ind_price = [Avg Price]

var total_price = CALCULATE([Avg Price],ALL('DIM Container'[ISO Code]))

var result = IF(filtered= TRUE(),ind_price,total_price)

return result

but this doesnt work for some reason .. when i try the individual variables it works but when i use it in the IF function .. when nothing is selected it still shows multiple lines 

anyone help? yea and it needs to be suitable for Direct Query so i can not create custom tables.. 

thank you 

[buchta]

hey @OwenAuger thanx for quick respond.. the solution looks great but I think its not possible with Direct Query... because of the creation of field parameter which is not supported for DQ

[OwenAuger]

Hi again @buchta 

Even if you have tables with partitions with DirectQuery storage mode, you should still be able to create field parameters (which are calculated tables with Import storage mode). Also if you're using composite models on Power BI or Analysis Services then you should definitely be able to create/use field parameters (in the local model).

I just tested this myself with a simple model with DirectQuery connection to SQL Server and added a field parameter.

Could you clarify which sources you are connecting to with DirectQuery and confirm whether Modeling > New Parameter > Fields is available when the model is open in Power BI Desktop?

Regards,

Owen

[OwenAuger]

Hi @buchta 

You can use a field parameter in the legend field of the visual, combined with a measure to filter it appropriately for the visual, so that it's filtered to "ISO Code" if ISO Code is filtered, otherwise filtered to "nothing". Similar to the method described here.

I have attached a sample PBIX to show how this works.

Steps I followed to set this up:

1. Create a field parameter Legend Field Parameter containing the ISO Code column, then edit the DAX expression to add an item named "BLANK" which does not have a valid field reference.

Legend Field Parameter =
{
    ( "ISO Code", NAMEOF ( 'DIM Container'[ISO Code] ), 0 ),
    ( "BLANK", "BLANK", 1 )
}

2. Create a measure called Legend flag. This measure returns 1 if the current field parameter should be used, otherwise 0.

Legend Flag = 
VAR CurrentField =
    MAX ( 'Legend Field Parameter'[Legend Field Parameter] ) -- Assume single selection
VAR ResultBoolean =
    IF (
        ISFILTERED ( 'DIM Container'[ISO Code] ),
        CurrentField = "ISO Code",
        CurrentField = "BLANK"
    )
VAR Result =
    INT ( ResultBoolean ) -- Convert to 0/1
RETURN
    Result

3. Create a visual containing 'Legend Field Parameter'[Legend Field Parameter] as the Legend field, and apply a Top N visual-level filter on Legend Field Parameter, set to "Top 1 by Legend Flag":

3. Now when the ISO column is filtered, the ISO code is used as the Legend field, otherwise there is no legend:

You can adjust the logic within the Legend Flag as needed to apply different conditions.

Does something like this work for you?

Regards