Barchart with constant line

[tvilla]

Hi everybody,

I'm stucked on this issue from a while and realized I need help to go further. 

I have two table with the follow headers:

1) ID, Date (yyyy-mm-dd), Type1, Type2, Value1

2) ID, Month(yyyy-mm-01), Value2

I would like to build a barchart with the follow features:

- the barchart shows the daily value1 for each ID in a specific month (both selectable through a filter)

- the barchart has a constant line showing value2 for the selected ID and month

- I need to allow filter by type1 and type2 (when a filter is selected the bar should go down while the constant line has to remain stable or disappear at all)

I hope you can help me with this task and wait for your feedback,

Thank you!

[v-shex-msft · Accepted solution]

HI @tvilla,

#1, You can refer to the following blog to create relationships based on multiple table fields. (create a calculated column with year, month part from the date field and concatenate with id, then extract them to create a bridge table with unique value to mapping two table records)

Relationship in Power BI with Multiple Columns - RADACAD

How to Join Many to Many with a Bridge Table in Power BI | Seer Interactive

#2, I'd like to suggest you create a measure formula to summary value based on current groups and use it as the constant line:

measure =
VAR selected =
    MAX ( Table1[Date] )
RETURN
    CALCULATE (
        SUM ( Table2[Value2] ),
        FILTER (
            ALLSELECTED ( Table2 ),
            YEAR ( [Date] ) = YEAR ( selected )
                && MONTH ( [Date] ) = MONTH ( selected )
        ),
        VALUES ( Table1[ID] )
    )

#3, You can try to do 'unpivot column' on type fields to send them to attribute and value, then you can simply use one filter to filter across multiple table fields.

Unpivot columns - Power Query | Microsoft Docs

Regards,

Xiaoxin Sheng