Forum Discussion

Denise_'s avatar
Denise_
New Member
3 years ago
Solved

Filter by selected rows

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's avatar
    tamerj1
    3 years ago

    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

7 Replies

    • Denise_'s avatar
      Denise_
      New Member
      3 years ago


      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:

      PositionnumberMonthValueValue (bins 0.2)
      12301.08.202310%0%
      45601.08.202350%40%
      12301.09.202340%40%
      45601.09.202310%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.)