Filter by selected rows

[Denise_]

Hi there,

i have two charts (histograms) about the same fact table. (Fields:  Positionnumber, Month, Value). Theres also dimTable for the Positiondata (Key positionnumber)
The first charts shows the count for this month, and the second chart shows the next month,

CountCurrentMonth = CALCULATE(COUNT('fact'[Positionnumber]), 'fact'[Month] = DATE(YEAR(today()), month(today()), 1), 'fact'[Value] >= 0,'fact'[Value] < 1)

CountNextMonth = CALCULATE(COUNT('fact'[Positionnumber]), 'fact'[Month] = DATE(YEAR(today()), month(today()) + 1, 1), 'fact'[Value] >= 0,'fact'[Value] < 1)

Now when i select the first bar in the first chart, PowerBi highlights the second chart based on the selected value (0%) of the first chart.

But what I'm trying to achieve is to get the second chart highlighted by the positionnumber, instead of the value. In other words, I want to see how positions with low % in this month are filled in the next month.


P.S.
I want to keep all other filters on the page, like a customer slicer (field from dim positiondata) and i want to avoid cross filtering, because i have a second fact table in my data model.

[tamerj1]

Hi @Denise_ 
I believe the following should be helpful

Using cross highlight with order and delivery date in Power BI - YouTube

[Denise_]

In the video he got 2 fields in the fact, which he wants to compare (order date / delivery date). And so he can duplicate his DimTable (dimDate) for his relations. I can't implement this, because I only have 1 field for the relations.

In my case, my fact table looks like:

Positionnumber	Month	Value	Value (bins 0.2)
123	01.08.2023	10%	0%
456	01.08.2023	50%	40%
123	01.09.2023	40%	40%
456	01.09.2023	10%	0%

When i select the first bar in the histogram for August (0-20%), i wanna see the value of the Positionnr "123" in the second chat (filtered for September).

I tried arround to use the DimPositiondata for that and a measure with crossfilter like

CountCurrentMonthCross = CALCULATE(
    COUNT( 'Positiondata'[Planposition]), 
    CROSSFILTER('Füllgrad'[Planposition], Positiondata[Planposition], Both), 
    'Füllgrad'[Month] = DATE(YEAR(today()), month(today()), 1), 
    'Füllgrad'[Value] >= 0,'Füllgrad'[Value] < 1
) 

CountNextMonthCross = CALCULATE(
    COUNT( 'Positiondata'[Planposition]), 
    CROSSFILTER('Füllgrad'[Planposition], Positiondata[Planposition], Both), 
    'Füllgrad'[Month] = DATE(YEAR(today()), month(today()) +1, 1), 
   'Füllgrad'[Value] >= 0,'Füllgrad'[Value] < 1
) 

But the second chart still gets filtered by the selected value of the x-axis when I select the first bar in the first chart.
I tried around with ALL(), ALLSELECTED(), REMOVEFILTERS(), etc. but this messes up the whole chart (all bars get the same size, etc.)

[tamerj1]

@Denise_ 
Can yu sahre a sample file?

[Denise_]

I uploaded a sample with less data to github

https://github.com/anybi/powerbi

[tamerj1]

@Denise_ 
Do you always have only two months?

If so then you can rearrange the table as follows. In this case I see no need for dimTables but of course I don't have the full picture to fully judge that.

Please refer to attached sample file and let me in case this does not comply with your requirements.

[Denise_]

Thank you for your efforts.

I don't always have only two months. I just wanted to make a minimal example as I can't share the original report.

I also have a chart for the next 3 months, for example, and I would like to use the filter here as well.

The dimensions are also minimized, I need them for the rest of the report.

[tamerj1 · Accepted solution]

@Denise_ 
Ok, then you can create a calculated column of the next month's bins as follows

You may select a month to analyze it along with the next month as follows

See attached sample file