topic Re: Filter by selected rows in DAX Commands and Tips

Filter by selected rows

Denise_ — Mon, 07 Aug 2023 08:08:28 GMT

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.

Re: Filter by selected rows

tamerj1 — Mon, 07 Aug 2023 08:55:59 GMT

Hi Denise_
I believe the following should be helpful

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

Re: Filter by selected rows

Denise_ — Mon, 07 Aug 2023 10:42:48 GMT

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.)

Re: Filter by selected rows

tamerj1 — Mon, 07 Aug 2023 11:34:20 GMT

Denise_
Can yu sahre a sample file?

Re: Filter by selected rows

Denise_ — Mon, 07 Aug 2023 12:23:36 GMT

I uploaded a sample with less data to github

https://github.com/anybi/powerbi

Re: Filter by selected rows

tamerj1 — Mon, 07 Aug 2023 16:11:53 GMT

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.

Re: Filter by selected rows

Denise_ — Mon, 07 Aug 2023 16:25:33 GMT

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.

Re: Filter by selected rows

tamerj1 — Tue, 08 Aug 2023 06:25:58 GMT

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