0

I have 4 selected values connected to dimDate and dimDate2 (copy)

  • dimdate(startyear,startmonth,
  • dimdate2 (endyear, endmonth)

enter image description here

connected to 2 columns startdate and enddate in another table

Here is the data

enter image description here

I want to be able to filterout rows (keep) that are in the selected period (Green) and exclude rows that haven't been open between this selected values.

enter image description here

-The easy answer here is to use Filter on this visualobject with startdate and enddate but you cant do that becouse if dynamic values , and you cant use created table becouse the is a dynamic selected values, you can't use new column to create a filter becouse the dynamic selected value

Whats left to do? I dont know maybe messurement. I have tyed to figuere this out for months, i have got some answer that is not right.

The I have the logic in SQL but i dont know how i can filterout rows using PowerBI with DAX. I can solve it with the things i said above (Without dynamic values) that you cant use.

Please help me figure out how i can filterout rows from a table. You don't need to solve the logic that i can do, just how i get rid of rows with logics.

enter image description here

enter image description here

Jonas
  • 185
  • 4
  • 16
  • There are several solutions to achieve that. It depends on several factors so before I star solving your problem I need to know the structure of the table. I am sure, there must be any kind of ID column, e.g. ProjectID or something that will describe the horizontal lines on the picture you've provided, doesn't it? – intruderr Apr 08 '22 at 15:16

1 Answers1

0

Working in HR, your business requirement here is almost verbatim with how we develop headcount measures. Headcounts, similarly need to be able to consider dynamic start and end dates, as applied via slicers.

Between your fact table and date-dimension table, establish two inactive relationships between your start and end date columns with the date column of your date table. (You only need one date table.) Once you have done that, you can create a measure similar to this one:

Active Timeframes = 
CALCULATE(
    (SOME EXPRESSION HERE), // e.g. COUNTROWS( 'Some Table' )
    FILTER(
        VALUES( 'Table'[Start Date] ),
        'Table'[Start Date] <= MAX( 'Date'[Date] )
    ),
    FILTER(
        VALUES( 'Table'[End Date] ),
        'Table'[End Date] >= MIN( 'Date'[Date] ) ||
        ISBLANK( 'Table'[End Date] ) // Can exclude if needed.
    )
)

While this example may not be 100% specific to your use case, it should be able to get you in the right ballpark. For more information on this implementation, see this YouTube video by Enterprise DNA.

codyho
  • 250
  • 1
  • 7
  • Thanks i made this work with row 3,4 Becouse i can give one example at the time, where can i put a OR inside? I want 4 different logics. Here is an example with 2 of them but i get this error ( Funktionen FILTER har använts i ett True/False-uttryck som används som tabellfilteruttryck. Detta tillåts inte. ) – Jonas Apr 11 '22 at 09:54
  • Mått2 = CALCULATE( COUNTROWS(TestTabell),OR( FILTER( VALUES(TestTabell[startdate]) ,AND(YEAR(TestTabell[startdate]) >= SELECTEDVALUE(tblDimDatum[År]),MONTH(TestTabell[startdate]) >= SELECTEDVALUE(tblDimDatum[Månad]))), FILTER( VALUES(TestTabell[enddate]), AND(YEAR(TestTabell[enddate]) <= SELECTEDVALUE(tblDimDatum2[År]),MONTH(TestTabell[enddate]) <= SELECTEDVALUE(tblDimDatum2[Månad])))), FILTER( VALUES(TestTabell[startdate]) ,AND(YEAR(TestTabell[startdate]) <= SELECTEDVALUE(tblDimDatum[År]),MONTH(TestTabell[startdate]) <= SELECTEDVALUE(tblDimDatum[Månad]))), FILTER( VA – Jonas Apr 11 '22 at 10:00
  • LUES(TestTabell[enddate]), AND(YEAR(TestTabell[enddate]) >= SELECTEDVALUE(tblDimDatum2[År]),MONTH(TestTabell[enddate]) >= SELECTEDVALUE(tblDimDatum2[Månad]))) ) – Jonas Apr 11 '22 at 10:00
  • what have i dont wrong with the OR ? or is it not possable? they are verry simalary but there is a > and a < in the other. – Jonas Apr 11 '22 at 10:06